Comparing Columns to each other

[Sung]

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

 

[PegL]

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.

 

[bj]

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.