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
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Max,
thank you for your valiant effort on this problem of mine. To comment on the solution you provided, i used your formulas and used conditional formatting but received results that were not accurate. Maybe if i emailed you the actual data set i am working with and showed to you how your formatting worked out, it would be easier. In the meantime, what happened was that in column B was that numbers ranging from 1 to 16 showed up in various places. What did you mean for this column to do? It had 1's next to values which i knew repeated in the negative, so i dont think that count was accurate. With column C, it basically counted each row position in order except for about 10 or 15 rows in the middle of my long dataset in column A. As for the conditional formatting, since column C did not work out exactly how i think you had anticipated, the formatting was also off. Although the formatting did what it was supposed to do, just that column C seemed to be of little use in this case. I will email you the dataset so that you can see for yourself, the results i achieved with your formulas. What exactly did you mean for columns B and C to do? I am not the best with understanding the code in excel. THank you again for all of your help in this matter. |
#8
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Here's a working sample to illustrate:
http://cjoint.com/?kbafj66Yrv NettOffPosnNeg.xls Note that the CF suggested earlier is to highlight cells is col A which *do not* cancel out. If you want to CF it the other way round, use the converse formula: =$C1="" The formulas in col B and C need to be implemented correctly. They need to be copied all the way down to the last row of data in source col A. My suggestion as applied to your sample is available he http://www.flypicture.com/download/MzcyODY= Madenning_Dilemma2_1.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message ups.com... Max, thank you for your valiant effort on this problem of mine. To comment on the solution you provided, i used your formulas and used conditional formatting but received results that were not accurate. Maybe if i emailed you the actual data set i am working with and showed to you how your formatting worked out, it would be easier. In the meantime, what happened was that in column B was that numbers ranging from 1 to 16 showed up in various places. What did you mean for this column to do? It had 1's next to values which i knew repeated in the negative, so i dont think that count was accurate. With column C, it basically counted each row position in order except for about 10 or 15 rows in the middle of my long dataset in column A. As for the conditional formatting, since column C did not work out exactly how i think you had anticipated, the formatting was also off. Although the formatting did what it was supposed to do, just that column C seemed to be of little use in this case. I will email you the dataset so that you can see for yourself, the results i achieved with your formulas. What exactly did you mean for columns B and C to do? I am not the best with understanding the code in excel. THank you again for all of your help in this matter. |
#9
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Some explanations:
Col B serves to assign an arbitrary unique "suffix" to the data in col A. This suffix assignment is necessary to distinguish between all multiple occurences of the source numbers which you have in col A. Eg there could be multiple occurences of 10, -10, 11, -11, etc all the way down in col A. Col C then checks for the match of the original source numbers in col A concatenated with the corresponding suffixes in col B against the full table array composed. Where it matches (ie cancels out), the expression returns a null string. Where it doesn't match, it returns an arbitrary row number as a flag. This flag can then be used in the CF formula to format col A for all the non-cancellations, ie all the unique source numbers in col A which do not cancel out The flag could also be used as well in another col E, to "float up" all the non-cancellations from col A. Easiest to see what's happening by referring to this small working sample (as posted earlier): http://cjoint.com/?kbafj66Yrv NettOffPosnNeg.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
|
|||
|
|||
Maddening Dilemma - Compare each cell within column a to each cell
Let's suppose your column that contains the values indicated is a
named range called MyRange. The following multi-cell array formula will return a unique occurence of each uncancelled item: =INDEX(IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange, "="&MyRange)- COUNTIF(MyRange,"="&-MyRange)), 0),SMALL(IF(MATCH(IF(MyRange0,ABS(MyRange)*(COUNT IF(MyRange,"="&MyRange)- COUNTIF(MyRange,"="&-MyRange)), 0),IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange,"="& MyRange)- COUNTIF(MyRange,"="&-MyRange)),0), 0)=ROW(INDIRECT("1:"&ROWS(IF(MyRange0,ABS(MyRange )*(COUNTIF(MyRange,"="&MyRange)- COUNTIF(MyRange,"="&-MyRange)), 0)))),MATCH(IF(MyRange0,ABS(MyRange)*(COUNTIF(MyR ange,"="&MyRange)- COUNTIF(MyRange,"="&-MyRange)), 0),IF(MyRange0,ABS(MyRange)*(COUNTIF(MyRange,"="& MyRange)- COUNTIF(MyRange,"="&-MyRange)),0), 0),""),ROW(INDIRECT("1:"&ROWS(IF(MyRange0,ABS(MyR ange)*(COUNTIF(MyRange,"="&MyRange)- COUNTIF(MyRange,"="&-MyRange)),0)))))) If there's an extra negative, it will return the negative, and if there's an extra positive, it will return the positive. One zero will be returned for all matching pairs. It will return #NUM error if your array range is longer than the list. So, in this set: 10 -10 -10 11 -11 12 13 14 -14 15 -15 -15 16 16 -16 The result is this, assuming you array-entered the above formula in an 8-row range: -10 0 12 13 -15 16 #NUM! #NUM! Hope this helps. -Ilia On Sep 28, 4:24 pm, wrote: 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. |
|
Thread Tools | |
Display Modes | |
|
|