Two lists, find *similar* cells (not duplicates) and mark/highlight, etc.

[Matt G.]

So I have 2 lists with client names. Since different salespeople
entered each data cell, the client names are spelled differently in
many of the entries. For example, some list client Bill Smith as: "B
Smith" "B. Smith" "Bill S." "Bill Smith"...you get the idea.

I want to locate (and eventually manipulate) these for *each* client.
Since there are hundreds of clients, I can't just use a CountIf formula
(for example) that looks for ranges that start with B or Bill, because
I'd have to create one for each client (e.g. now look for ranges that
start with S for Susan).

Is there a formula (or maybe I'd need a macro) that would: look at its
corresponding cell (formula in C2 would look at A2) and take the first
3 letters (or 2 letters, or 5 letters) and match those with the
clientnames (cells) from the second list (in columb B)?

 

[Bernard Liengme]

Please clarify what data is in column A and in column B so your last para is
more meaningful.
By the way is there a column with zipcodes or phone numbers?
To avoid this in future, use separate columns for fist and last name; you
can always concatenate with the & operator ( =A2&" "&B2) - ain't hindsight
great!
best wishes

 

[Matt G.]

Thanks Bernard. So column A has existing client names for the last 5
years. Column B has the client names that have done NEW business in
the last 6 months (but not necessarily by new clients). So the names
in column B are a mix of 1.) new names (that don't appear in list A)
and 2.) existing ones (that do appear in list A).

I'm trying to find out which names from the list of new business
(column B) are existing clients that are listed in column A (but might
be typed in differently--e.g. B. Smith instead of Bill Smith).

 

[Guest]

Addresses, or phone numbers may be easier to identify....there are just too
many different possibilities with the names........you could get there
eventually maybe by using something like =left(A1,5) down the left side of
your lookup table and then something like
=VLOOKUP(left(A1,5),MyTable,1,false) to do the search....but it will still
take much hand intervention before it's over...........or maybe using the
AutoFilter and filtering for "contains Smith", might help

hth
Vaya con Dios,
Chuck, CABGx3