MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Comparing 2 columns in Excel


Posted by S on October 05, 2001 9:03 AM

How do I compare 2 columns of text data in an excel spreadsheet?

Posted by Eric on October 05, 2001 9:18 AM

Could you give an example of the your columns and the results you would like to see? (NT)

Posted by Keith on October 05, 2001 9:32 AM

Hi S,
If you'll paste the below code in the topmost cell of the column you'd like to get comparion results in and then edit the A1 and B1 to reflect the columns in your sheet that you'd like to compare, then an "N" will be returned if the cells aren't equal and a blank space " " will return if they are equal. To compare the whole column, simply highlight the cell with the formula and corner drag as far down as necessary. You can also adjust output by changing the N between the quotes to whatever you want and adding anything between the second set to signify a match.

=IF(A1<>B1,"N"," ")

(essentially, is says if A1 is not equal to B1, then print an "N" else print a blank " ".)

Hope this helps,
Keith

Posted by Loren on October 05, 2001 9:50 AM

comparing 2 columns: 3 EZ ways

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
=IF(ISERROR(VLOOKUP(A1,$B$1:$B$2000,1,FALSE)),A1,"")
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
=ISNUMBER(MATCH(LEFT(A1,9)&"*",List2!A:A,0))+0

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".