Back to Forms in Excel VBA archive index

Back to archive home

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

Check out our Excel Resources | ||||

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

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.

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.