Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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?

Check out our Excel Resources

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

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


Re: Comparing 2 columns in Excel

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


comparing 2 columns: 3 EZ ways

Posted by Loren on October 05, 2001 9:50 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
=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".


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.