Solved Fuzzy Lookup/vlookup to match lists

August 2, 2017 at 07:02:03
Specs: Windows 7
I am hoping that someone can help me with this excel issue. I have two sheets in a excel workbook, one with a list of name of individuals participating in one program, the other with a list of names of individuals participating in a different program. The challenge is that each program is recording names differently. For example, the first program records names as: Doe, John A and the other Doe, John Anthony. In both lists they are recording date of birth in the same fashion, i.e. 12/25/1977. However, I do have concerns that it is possible that a clerical error could have occurred at the time of the data entry and that this could be incorrect and since there are hundreds of entries, it is likely that multiple people have the same birth date. Both list use an ID number but the format of these have nothing in common with one another. I am hoping to combine these two lists as the person the individual is working with in each program is listed in each list and would like to create a master list of those who are in both programs and who they are working with. I have created samples of each list below. To this point, I have tried using vlookup to accomplish this as well as the fuzzy lookup add-in, but when I review the information, there are clearly individuals that are being missed that I would think would be found. Any suggests you might have would be greatly appreciated.

SheetA

Name	                     ID#                DOB                    Agent
Doe, John A	          X47875             12/25/1977	             Todd Smith
Doe, Jane B.	          C25369             11/22/1989	             Tom Hass
Smith, Donna J            F78965              5/11/1965	             Todd Smith

SheetB

Name                         ID#                 DOB                    Officer
Doe, John Anthony           34567               12/25/1977             Chad Towns
Doe, Jane Betrice           98765               11/22/1989             Steven Young
Smith, Donna Jane           23678               5/11/1965              Tom Doe

Thank you in advance for assistance!


See More: Fuzzy Lookup/vlookup to match lists

Reply ↓  Report •

#1
August 2, 2017 at 18:01:54
✔ Best Answer
You didn't provide any column letters or row numbers, so I am going to assume your data looks like this:

        A               B            C              D
1      Name             ID#         DOB           Agent
2   Doe, John A	      X47875     12/25/1977	 Todd Smith
3   Doe, Jane B.      C25369     11/22/1989	 Tom Hass
4   Smith, Donna J    F78965     5/11/1965	 Todd Smith

Based on that assumption...

1 - Insert a Helper Column at Column A.
2 - Enter this formula in the new A2:

=IFERROR(LEFT(B2,FIND(" ",B2,FIND(" ",B2)+1)+1),B2)

3 - Drag this down as far as you need.

For names with a middle initial or middle name, you should get the name but only the middle initial.

e.g.

For Doe, John Anthony or Doe, John A the formula will return Doe, John A


For names that have no middle initial or middle name, you should get the existing name.

e.g.

For Doe, John the formula will return Doe, John

Now you should have matching names in Column A and you can apply a standard VLOOKUP against that column. i.e both lists will contain Doe, John A

Granted, there are probably formats of names for which this formula may not work.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •
Related Solutions


Ask Question