MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing 2 columns

Posted by Karen on July 17, 2001 4:15 AM

I need to take 2 different columns in 2 different worksheets, compare the 2 and add whatever does NOT match to the original spreadsheet. How do I do this?

Posted by Loren on July 17, 2001 6:29 AM

given 2 lists of words

1. Select column B (click the letter B)
2. Choose Format > Conditional formatting
" 3. In ""Condition 1"" choose ""Formula is"""
" 4. Enter this formula =(COUNTIF($A:$A,B1)=0)*(B1<>"")"
" 5. Click the ""Formats"" button and take your pick. OK.OK."

Now every name in B not in A will light up.

Another example
comparison of 2 columns
this function put in c1 and autofilled down will show the A1 value,
IF A1 does not exist anywhere in B1:B2000 -

3rd example...
Reply to: Compare two columns using wildcards

where A1 is a value from the 1st column and List2!A:A is
a reference to your 2nd column. This formula will return
0 or 1. 1 means that there's a match; 0 means there isn't.
You can apply a number format to change these values to any
flag you wish. For example, [=1][green]"Y";[red]"N" will
produce a green "Y" or a red "N".