Best Approach In Dealing Irregular Formats of Names

[bulletproof]

Guys,

Please help.

I have 2 separate data sources with irregular name formats...

data source 1 may show:
Last name, First name
Last name ,First name
Last name , First name
Last name, First name Middle name
Last name ,First name Middle initial

data source 2 may show:
Last name, First name
Last name, First name Middle initial

I need to see who from data source 1 appears in data source 2.
Please tell what is the best approach to accomplish this?

should I text to columns each source then compare with vlookup or
the sumproduct formula posted here? (if ever it's possible)

I did a vlookup but ended up a few lots of names in data source 1 not
appearing in data source 2.

I hope I make sense.
please help.

thanks a lot.

 

[VoG]

Maybe a fuzzy lookup http://www.mrexcel.com/board2/viewtopic.php?p=974873#974873

 

[Jonmo1]

I would think cleaning up the source1 data would be wise...

Use Text to columns with comma as the delimiter to seperate last name from first name (middle initial).

Now suppose that ends up with
ColA = Last name
ColB = First name middle initial

Then in column C put

=TRIM(A1)&", "&TRIM(B1)

Now your source1 data is normalized.

 

[Jonmo1]

Or all in one formula, skipping the text to columns

=TRIM(LEFT(A1,FIND(",",A1)-1))&", "&TRIM(MID(A1,FIND(",",A1)+1,LEN(A1)))

 

[bulletproof]

Hi VoG,

the FUZZYVLOOKUP made me feel dizzy... to much advance for an old dog like me... will play with it sometimes, thanks man.

Jonmo1,

I'm playing with the all in one formula now, will let you know if I'm successful.

Thanks again guys for your help.

 

[bulletproof]

Hey jonmo1,

data 1 source is now normalized.

you see, the complete list is data source 1, these people are required to do a job, and the way for me to see if they completed the job is by using data source 2.

I'm looking for a way in excel that would make me "see" which folks from the data source 1 list completed the job by using data source 2.

I know it would be easier if both sources have a common identifier, but unfortunately, they don't have any. just the names that were irregularly formatted.

I'm using 2003 excel if that helps...

thanks again.

 

[Jonmo1]

Not sure I understand what you're after now...

Do you just want to see if the names in Source1 exist in Source2 ?

Try

=ISNUMBER(MATCH(A1,B$1:B$1000,0))

A1 is a name in Source1
B1:B1000 is the list of names for Source2

 

[bulletproof]

No sir, sorry for being vague.

I need to see a match like if a person in data source 1 completes the job as per comparison with data source 2, that name should appear as the same format in.

like:

last name first name = last name, first name
last name, first name = last name, first name
last name ,first name = last name, first name
last name, first name MI = last name, first name MI
last name, first name middle name = last name, first name middle name

I hope this makes sense.

Or, can I just "text to columns" both data sources, then use vlookup to search each columns and find a match?