Comparing 2 lists in Excel

[Todd]

I have tried, unsuccessfully, to use conditional
formating to compare 2 lists of names to find duplicates.
Is there any other way to compare 2 lists? All I would
need is to highlight the duplicates.

It would also be helpful to know if there is a way for me
to allow the comparison to be for any words, not the
exact contents of a cell. I remember a 'set exact off'
command from the DBIII days - is there any such command
in Excel?

I am trying to compare 2 lists of companies, and one or
both list might nit have the exact name entered.

Thanks in advance for your help.

 

[Jane Graves]

I would suggest a VLOOKUP formula, but that will only take
care of exact matches for text. I would do the VLOOKUP
first to take care of exact matches. This formula is
detailed extensively in the help files for the program.
Check list a to list b to determine duplicates from list a.
VLOOKUP(LISTA!A3,LISTB!$A$3:$A$100,1,FALSE). Duplicates
will then appear as the name and non-duplicates as #NA.

They cannot be highlighted, but you can assign a value in
this cell which can be sorted on later:

IF(ISERROR(VLOOKUP(LISTA!A3,LISTB!
$A$3:$A$100,1,FALSE)),"","Duplicate")

This is actually better than highlighting because it
allows for sorting based on the value rather than strict
visual confirmation. You can always sort by that value
later, add the highlighting for the row, and resort by
name.

This will take care of exact matches only. After that, my
suggestion would be to copy the names from both lists into
a workbook, adding a column showing the list of origin.
Then sort based on name and do a visual check. There are
too many variables to do this automatically (misspelling,
addition of "Inc" or "Ltd", abbrevations). You can then
manually tag duplicates with a "Duplicate" tag so they
match the ones previously determined. Only you will be
able to determine true duplicates. I hope that the lists
are not so long as to make this process too much of a
burden.

Jane

 

[Jason Morin]

Let's assume 1 list in column A (A1:A4), the other in col.
B, and you wish to highlight any items in column B that
contain the first 5 letters of any of the items in col. A.

1. Select column B.
2. Go to Format > Conditional Formatting and select
Formula Is on the drop-down list.
3. Insert this formula:
=SUMPRODUCT(ISNUMBER(SEARCH(LEFT($A$1:$A$4,5),$B1))*1)
4. Format as desired.

HTH
Jason
Atlanta, GA