If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches
I have been trying to find a way in which i can have excel search
through two columns to find each unique match between the columns and somehow change the formatting (like highlighting) to fish out the matches. The problem is that i cannot think of a way to get each cell in column a compared to each cell in column b, rinse and repeat for the entire column a. And i am talking about thousands of rows per column, and the columns are not necessarily the same size. Maddening! If anyone has any ideas that do not involve installing shareware plugins, please let me know! Thanks! |
#2
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
use countif as the formula is part of Conditional Formatting
=countif(C:C,A2) information on conditional formattiong Debra Dalgleish http://www.contextures.com/tiptech.html Chip Pearson's site on duplicates and uniques http://www.cpearson.com/Excel/Duplicates.aspx -- Regards, Tom Ogilvy " wrote: I have been trying to find a way in which i can have excel search through two columns to find each unique match between the columns and somehow change the formatting (like highlighting) to fish out the matches. The problem is that i cannot think of a way to get each cell in column a compared to each cell in column b, rinse and repeat for the entire column a. And i am talking about thousands of rows per column, and the columns are not necessarily the same size. Maddening! If anyone has any ideas that do not involve installing shareware plugins, please let me know! Thanks! |
#3
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Tom,
A great start upon a solution, i am attempting a workaround using the info you provided, many thanks! Although my problem is more intricate than i let on. Realistically, i would like to find duplicate values within the same column, but some of the values are negative and some ar positive. I need to find out, and highlight, the values which would cancel each other out. Hence i need to find duplicates, except that they are not EXACT duplicates, but one is the positive version of the number and one is the negative version of the number. Here is an small example of such a list: A1 10 -matches such as this and the negative 10 below it both need to be highlighted -10 11 12 13 -does not cancel and doesnt need to be highlighted -12 -11 15 -15 -15 -also does not cancel since one positve already canceled with one negative and doesnt need to be highlighted as you can see, i am attempting to highlight the corresponding positive and negative values (the ones which would cancel each other out) and leave the singular values unhighlighted. The problem is even futher complicated by the fact that some values might have multiple duplicates. There may be 10 positive number 11's and nineteen negative number 11's, meaning i need the 10 positive versions of the number 11 to cancel and highlight along with the 10 negative versions of the number 11, but to leave the other 9 number 11's unhighlighted. I apologize for how confusing this sounds, but i would choose confusing over hours of manual labor infused with human error anyday, seeing as how i have thousands of rows within this one column with many random and changing values to identify and compare. If you or anyone can think of a solution more closely tailored to this particular situation, i would be indebted to you forever. In the meantime, i will work with what you provided to me Tom. Thank you kindly. |
#4
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Also, to clarify, the reason i was asking about two columns was
because i used absolute value on all of the negative numbers and put them in a separate list next to the positive ones to find EXACT matches. But if there is a way in which this wasnt necessary, that would be far more ideal. Thanks again guys! |
#5
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Are you allowed to sort the data, as well as taking the absolute
value? If so, then you are looking for pairs of numbers in adjacent cells. Assuming that you have your ABS formula in column B, starting with B1, and that you have sorted the data by column B, then put this formula in C1: =IF(B1=B2,"yes1","no") and this one in C2: =IF(AND(B2=B1,C1="yes2"),IF(B2=B3,"yes1","no"),IF( B2=B1,"yes2",IF(B2=B3,"yes1","no"))) Copy this formula down column C by double-clicking the fill icon (the small black square in the bottom right corner of the cursor. It will give you pairs of "yes1"/"yes2" down the column indicating paired duplicates, and the occasional "no" meaning a single unpaired (unique) value. If you wanted to use conditional formatting on the values in column A, then you can use Formula Is and then =LEFT(C1,3)="yes" and set your colour. I've just realised that this doesn't necessarily pair +10 with -10, so if you have, say, three +10s and one -10 this would indicate all four 10s would be paired - does this matter? If you wanted the data in the original order, then you should first enter a simple sequence in column D (say), i.e. 1, 2, 3, 4 etc before sorting the data (including column D) on column B and using the formulae. Then fix the values in column C and re-sort the data by column D to get it back to the original sequence - column D can then be deleted. Anyway, hope this helps. Pete On Sep 28, 9:41 pm, wrote: Also, to clarify, the reason i was asking about two columns was because i used absolute value on all of the negative numbers and put them in a separate list next to the positive ones to find EXACT matches. But if there is a way in which this wasnt necessary, that would be far more ideal. Thanks again guys! |
#6
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
One play which can deliver both the CF desired and a way to extract the
cells in col A which do not cancel out .. Assuming source numbers within A1:A100, as posted Put in B1: =IF(A1="","",COUNTIF($A$1:A1,A1)) Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"",ROW() )) Select B1:C1, copy down to C100. Col C will "flag" only cells in col A which do not cancel out. Then you could easily apply CF to highlight col A pointing to col C Select col A (A1 active), then apply CF using Formula Is: =$C1"" Format the fill color to taste, OK out And if you want to extract those cells in col A which do not cancel out in another col (this might be useful), just put in say E1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) )) Copy down to E100. This will return all cells in col A which do not cancel out, neatly bunched at the top in col E -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ps.com... Tom, A great start upon a solution, i am attempting a workaround using the info you provided, many thanks! Although my problem is more intricate than i let on. Realistically, i would like to find duplicate values within the same column, but some of the values are negative and some ar positive. I need to find out, and highlight, the values which would cancel each other out. Hence i need to find duplicates, except that they are not EXACT duplicates, but one is the positive version of the number and one is the negative version of the number. Here is an small example of such a list: A1 10 -matches such as this and the negative 10 below it both need to be highlighted -10 11 12 13 -does not cancel and doesnt need to be highlighted -12 -11 15 -15 -15 -also does not cancel since one positve already canceled with one negative and doesnt need to be highlighted as you can see, i am attempting to highlight the corresponding positive and negative values (the ones which would cancel each other out) and leave the singular values unhighlighted. The problem is even futher complicated by the fact that some values might have multiple duplicates. There may be 10 positive number 11's and nineteen negative number 11's, meaning i need the 10 positive versions of the number 11 to cancel and highlight along with the 10 negative versions of the number 11, but to leave the other 9 number 11's unhighlighted. I apologize for how confusing this sounds, but i would choose confusing over hours of manual labor infused with human error anyday, seeing as how i have thousands of rows within this one column with many random and changing values to identify and compare. If you or anyone can think of a solution more closely tailored to this particular situation, i would be indebted to you forever. In the meantime, i will work with what you provided to me Tom. Thank you kindly. |
#7
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
M/s Pogster,Pete , Max and Tom,
Hi everybody. I am a learner in usage of Excel functions as well as VBA. I hope the Excel function solutions offered by you experts will provide me more insights. Meanwhile, I looked at Pogster's problem as a VBA problem and tried to write a programme ( the first full fledged one I am writing struggling with the codes & Help feature in VBA). I, understand his requirement is to mark off one value with equivalent negative value whenever it appears in the data range. My other assumptions we i) Even a neagative value may precede a positive value. ii) If there is only a pair of positive values or negative value both will remain unhighlighted. iii) The first opposite value has to be the basis for markoff iv)The marked off entries have to have colored so that they can be distinguished from the outstanding ones v) The programme has to proceed to look into the entire range until it encounters a blank cell in the same column. I have called the programme markOff. It has to be run through VBA say using F5. Before commencing the execution, the cursor has to be kept on the first cell of the range in the excel sheet. I have not prepared any code for a button or for a message box (say, for asking the user whether the cursor is in the first cell and if not to keep it there to proceed further), as writing this programme itself has been almost a day's job taking away my weekend and I am also required to learning coding for buttons and msgbox. The code is given below. I request the experts to look into it and suggest improvement, if any needed to make it more efficient and economical. The data I have taken as the basis for testing the programme is given first, followed by the programme. A -12 12 15 14 -15 13 -16 15 16 13 16 17 (intentionally left blank to see whether execution stops here or not) 17 The code: ---------------------------------------------------------------- Sub markOff() ' ' markOff Macro ' Macro recorded 29/09/2007 by Balan ' Dim Num As Range Dim Val As Double Dim addr As String Dim rownum As Integer Dim colnum As Integer rownum = ActiveCell.Row + 1 colnum = ActiveCell.Column addr = ActiveCell.Address Val = ActiveCell.Value Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Do While ActiveCell.Value "" If ActiveCell.Value = -Val Then If ActiveCell.Interior.ColorIndex = 6 Then rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) addr = ActiveCell.Address Val = ActiveCell.Value rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Else ActiveCell.Interior.ColorIndex = 6 Range(addr).Interior.ColorIndex = 6 Application.Goto Reference:=Worksheets("Sheet1").Range(addr) rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Do While ActiveCell.Interior.ColorIndex = 6 rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Loop addr = ActiveCell.Address Val = ActiveCell.Value rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) End If Else rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) If ActiveCell.Value = "" Then Application.Goto Reference:=Worksheets("Sheet1").Range(addr) rownum = ActiveCell.Row + 1 If ActiveCell.Value "" Then Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Do While ActiveCell.Interior.ColorIndex = 6 rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Loop addr = ActiveCell.Address Val = ActiveCell.Value rownum = ActiveCell.Row + 1 Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) End If End If End If Loop End Sub --------------------------------------------- I hope I am not troubling you. Balan "Pete_UK" wrote: Are you allowed to sort the data, as well as taking the absolute value? If so, then you are looking for pairs of numbers in adjacent cells. Assuming that you have your ABS formula in column B, starting with B1, and that you have sorted the data by column B, then put this formula in C1: =IF(B1=B2,"yes1","no") and this one in C2: =IF(AND(B2=B1,C1="yes2"),IF(B2=B3,"yes1","no"),IF( B2=B1,"yes2",IF(B2=B3,"yes1","no"))) Copy this formula down column C by double-clicking the fill icon (the small black square in the bottom right corner of the cursor. It will give you pairs of "yes1"/"yes2" down the column indicating paired duplicates, and the occasional "no" meaning a single unpaired (unique) value. If you wanted to use conditional formatting on the values in column A, then you can use Formula Is and then =LEFT(C1,3)="yes" and set your colour. I've just realised that this doesn't necessarily pair +10 with -10, so if you have, say, three +10s and one -10 this would indicate all four 10s would be paired - does this matter? If you wanted the data in the original order, then you should first enter a simple sequence in column D (say), i.e. 1, 2, 3, 4 etc before sorting the data (including column D) on column B and using the formulae. Then fix the values in column C and re-sort the data by column D to get it back to the original sequence - column D can then be deleted. Anyway, hope this helps. Pete On Sep 28, 9:41 pm, wrote: Also, to clarify, the reason i was asking about two columns was because i used absolute value on all of the negative numbers and put them in a separate list next to the positive ones to find EXACT matches. But if there is a way in which this wasnt necessary, that would be far more ideal. Thanks again guys! |
#8
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
Mr Tom
Pl see my response to this question. I shall be grateful for your comments / suggestions. "Tom Ogilvy" wrote: use countif as the formula is part of Conditional Formatting =countif(C:C,A2) information on conditional formattiong Debra Dalgleish http://www.contextures.com/tiptech.html Chip Pearson's site on duplicates and uniques http://www.cpearson.com/Excel/Duplicates.aspx -- Regards, Tom Ogilvy " wrote: I have been trying to find a way in which i can have excel search through two columns to find each unique match between the columns and somehow change the formatting (like highlighting) to fish out the matches. The problem is that i cannot think of a way to get each cell in column a compared to each cell in column b, rinse and repeat for the entire column a. And i am talking about thousands of rows per column, and the columns are not necessarily the same size. Maddening! If anyone has any ideas that do not involve installing shareware plugins, please let me know! Thanks! |
#9
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
Mr.Max,
Pl see my response to this question. I shall be grateful for your comments / suggestions. "Max" wrote: One play which can deliver both the CF desired and a way to extract the cells in col A which do not cancel out .. Assuming source numbers within A1:A100, as posted Put in B1: =IF(A1="","",COUNTIF($A$1:A1,A1)) Put in C1, then array-enter the formula by pressing CTRL+SHIFT+ENTER (instead of just pressing ENTER): =IF(B1="","",IF(ISNUMBER(MATCH(-A1&"_"&B1,$A$1:$A$100&"_"&$B$1:$B$100,0)),"",ROW() )) Select B1:C1, copy down to C100. Col C will "flag" only cells in col A which do not cancel out. Then you could easily apply CF to highlight col A pointing to col C Select col A (A1 active), then apply CF using Formula Is: =$C1"" Format the fill color to taste, OK out And if you want to extract those cells in col A which do not cancel out in another col (this might be useful), just put in say E1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) )) Copy down to E100. This will return all cells in col A which do not cancel out, neatly bunched at the top in col E -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ps.com... Tom, A great start upon a solution, i am attempting a workaround using the info you provided, many thanks! Although my problem is more intricate than i let on. Realistically, i would like to find duplicate values within the same column, but some of the values are negative and some ar positive. I need to find out, and highlight, the values which would cancel each other out. Hence i need to find duplicates, except that they are not EXACT duplicates, but one is the positive version of the number and one is the negative version of the number. Here is an small example of such a list: A1 10 -matches such as this and the negative 10 below it both need to be highlighted -10 11 12 13 -does not cancel and doesnt need to be highlighted -12 -11 15 -15 -15 -also does not cancel since one positve already canceled with one negative and doesnt need to be highlighted as you can see, i am attempting to highlight the corresponding positive and negative values (the ones which would cancel each other out) and leave the singular values unhighlighted. The problem is even futher complicated by the fact that some values might have multiple duplicates. There may be 10 positive number 11's and nineteen negative number 11's, meaning i need the 10 positive versions of the number 11 to cancel and highlight along with the 10 negative versions of the number 11, but to leave the other 9 number 11's unhighlighted. I apologize for how confusing this sounds, but i would choose confusing over hours of manual labor infused with human error anyday, seeing as how i have thousands of rows within this one column with many random and changing values to identify and compare. If you or anyone can think of a solution more closely tailored to this particular situation, i would be indebted to you forever. In the meantime, i will work with what you provided to me Tom. Thank you kindly. |
#10
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each
Balan,
Thanks so much for taking the time to write a script for this dilemma. In my attempts to execute the VBA macro script in excel, i recieved a compile erorr: syntax error which caused the macro not to run. The error occured at this point: Application.Goto Reference:=Worksheets("Sheet1").Cells(rownum, colnum) Beneath the first DoWhile Loop. I am not much a programmer and so am not sure as to why this happened. |
Thread Tools | |
Display Modes | |
|
|