Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Columns to each other
Hi everyone,
I had a question about comparing columns....is it possible to compare columns to each other. So, for example... Column1 Column2 Apple Orange Cherry Apple Orange Grape Melon Melon Grape Strawberry I want excel to compare the two columns and list me the ones that do not match. So the results would be Cherry and Strawberry. I'm not concern on how the out put looks. I just have to find out what dropped off from the first column to the second and what was added on. Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Columns to each other
I would probably use Vlookup (twice). You could embed it in an if statement
to make the result more readable. ex: Lists in L2:M6. In L2: =IF(ISNA(VLOOKUP(L2,$M$2:$M$6,1,FALSE)),L2,""), and copy down. In M2: =IF(ISNA(VLOOKUP(M2,$L$2:$L$6,1,FALSE)),M2,""), and copy down. -- Good Luck, Peg "Sung" wrote: Hi everyone, I had a question about comparing columns....is it possible to compare columns to each other. So, for example... Column1 Column2 Apple Orange Cherry Apple Orange Grape Melon Melon Grape Strawberry I want excel to compare the two columns and list me the ones that do not match. So the results would be Cherry and Strawberry. I'm not concern on how the out put looks. I just have to find out what dropped off from the first column to the second and what was added on. Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing Columns to each other
to highlight the ones which have changed you could use conditioonal formating
if column 1 and 2 are A and B highlight column A <format< Conditonal formatting change value is to formula is =countif(B:B,A1)=0 format-pattern-red select OK highlight column B condtional formatting Formula is =Countif(A:A,B1)=0 format-pattern-green If you have multiple adjacent columns you want to do this with, you can have up to three conditional format options besides no change if you use more than one, the first found which is met is the one which will be in effect so I would set the first condition as formula is =and(Countif(A:A,B1)=0,Countif(C:C,B1)=0) Format-pattern-orange and have conditons 2 and 3 for the other appropriate formulas and formats. "Sung" wrote: Hi everyone, I had a question about comparing columns....is it possible to compare columns to each other. So, for example... Column1 Column2 Apple Orange Cherry Apple Orange Grape Melon Melon Grape Strawberry I want excel to compare the two columns and list me the ones that do not match. So the results would be Cherry and Strawberry. I'm not concern on how the out put looks. I just have to find out what dropped off from the first column to the second and what was added on. Any help would be appreciated. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing two columns of information with 2 new columns of informa | Excel Discussion (Misc queries) | |||
comparing two columns | Excel Discussion (Misc queries) | |||
comparing columns | Excel Worksheet Functions | |||
Comparing columns | Excel Discussion (Misc queries) | |||
comparing two columns? | Excel Discussion (Misc queries) |