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 |
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 |
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 |
All times are GMT +1. The time now is 11:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com