Matching up non identical lists of names

[gmittar]

Hi All,

I'm racking my brains for a good solution to a seemingly simple problem. I have two lists of names, one is last, first, and the other is first last.

My problem is that the lists are not consistent. Some of the Last,First have spaces between in the middle, some do not. Some on my First Last list are actually Last First. I feel like this is a classic personnel management issue. When people are relied on to enter employee names, there's rarely consistency in how they do it.

My end goal is to add employee numbers from the Last/First list to the First/Last list.

Does anyone have a good solution to something like this? Example file in the link.

https://sagehospitalityllc.box.com/s/u3uf5mmqtprwrtjwtrrdrz44xta8lapc

 

[jtakw]

Hi,

I was unsure exactly how your Actual data is laid out, and how you want the results...
You had both Columns of names all lined up in your sample, so I threw in a few that's not in "order":

Book1
	A	B	C	D
1	First/Last		Last/First	EE #
2	James Redigan	10001	Redigan,James	10001
3	Donald Duck	NO MATCH	Mouse,Mickey	33333
4	John ,Doe	99999	Rogers , Roy	77777
5	Charles Ford	10002	Ford,Charles	10002
6	William Blakely	10003	Blakely, William	10003
7	Richard Freeman	10004	Freeman,Richard	10004
8	Scott, James	10005	James,Scott	10005
9	Chuck Darford	10006	Darford , Chuck	10006
10	Curt Froseth	10007	Froseth,Curt	10007
11	Ben Padilla	10008	Padilla, Ben	10008
12	Gonzales, Juan	10009	Juan,Gonzales	10009
13	Frank Fink	10010	Fink,Frank	10010
14	Roy Rogers	77777	Doe, John	99999
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",""),SUBSTITUTE(SUBSTITUTE(C$2:C$14&C$2:C$14,",","")," ","")),D$2:D$14),"NO MATCH")

B2 formula copied down.

 

[gmittar]

Thank you!

That gets me most of the way. The last place that it seems to break is when there is a middle initial on the list with the employee numbers.

So, if John Doe was listed at Doe,John A it would return No Match.

Can you think of a way around that? Otherwise, this has already cut my work significantly.

Thanks again!

 

[jtakw]

This updated formula will handle Middle initials in the Last/First name column (Column C) that are Not present in First/Last name column (Column A)
Please note, if there are more than one person with the Same First and Last name, but Different Middle initial, you will get incorrect results.

Book1
	A	B	C	D
1	First/Last		Last/First	EE #
2	James Redigan	10001	Redigan,James	10001
3	Donald Duck	NO MATCH	Mouse,Mickey	33333
4	John ,Doe	99999	Rogers , Roy	77777
5	Charles Ford	10002	Ford,Charles	10002
6	William Blakely	10003	Blakely, William	10003
7	Richard Freeman	10004	Freeman,Richard	10004
8	Scott, James	10005	James,Scott	10005
9	Chuck Darford	10006	Darford , Chuck	10006
10	Curt Froseth	10007	Froseth,Curt	10007
11	Ben Padilla	10008	Padilla, Ben	10008
12	Gonzales, Juan	10009	Juan,Gonzales	10009
13	Frank Fink	10010	Fink,Frank	10010
14	Roy Rogers	77777	Doe, John	99999
15	Jack Doe	55555	Doe, Jane A	22222
16	Jane Doe	22222	Doe Jack A	55555
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(LOOKUP(2,1/SEARCH(SUBSTITUTE(SUBSTITUTE(A2,",","")," ",""),SUBSTITUTE(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(C$2:C$16,","," "))," ",REPT(" ",100)),200)&LEFT(SUBSTITUTE(SUBSTITUTE(C$2:C$16,","," ")," ",REPT(" ",100)),100)," ","")),D$2:D$16),"NO MATCH")

 

[gmittar]

Awesome, thanks so much!

 

[jtakw]

You're welcome.