Back to Forms in Excel VBA archive index

Back to archive home

SITUATION: I have a column of account names (text) with approximately 3000 rows. I would like to compare column with another column, and identify (i.e. flag) those account names that exist in both columns (duplicates).

QUESTION: How do I get Excel to identify (preferably by highlighting) those entries that are listed in BOTH column #1 AND column #2?

NOTE: The columns do not share the same case (Column #1 is a mixture of small-caps and large-caps, while Column #2 is all capital letters) - does this matter?

Thanks!

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.