Match sheets by Name, Date of Birth, and/or SSN

**bradulet** · 06-03-2013, 12:37 PM

I need to match two groups as close as possible and the data I have is First Name (A), Last Name(B), Date of Birth (C), Social Security Number(D). I have other extra columns in my files which I want to keep. I would like the match to be on 2 different levels: 1) by Social Security Number OR 2) by a combination of the first 3 letters of First Name or Last Name AND date of birth. The final matched worksheet will have: Sheet1 - matched records, Sheet2 - unmatched from the first file, Sheet3 - unmatched from the second file. (I will have to use this procedure for other files too but they will all have A,B,C,D defined as above). I am not very familiar with Visual Basic...so please, be patient with me. See attached an example of file that I have to match (in reality, there are thousands of records). Thank you, Bradulet

**tehneXus** · 06-03-2013, 05:28 PM

Hi,

I have a few questions:
1. The columns headings in the sheets "FirstFile" and "SecondFile" are not consistent, the names are different and the order too, how should a program match this?

2. You are talking about "Files" all the time, is the data located in different files or are those just sheets? If these are files, where should the output go to?

3. I found 3 matches by SSN but no one using a combination of birth date and 1st 3 letter of name, is that correct?

**MarvinP** · 06-03-2013, 05:41 PM

Hi bradulet and welcome to the forum,

Find the attached with a few helper columns on Sheet1 and some VLookup formulas on Sheet2 that I think are what you are looking for.

**bradulet** · 06-03-2013, 06:12 PM

Sorry tehneXus, I put one file in one sheet and named it "FirstFile" and the other one in another sheet "SecondFile" thinking that it would be easier to have everything in one place. I think there are only 2 matches by ssn... The output would go in the same workbook.
As for not naming the columns exactly...sigh...no excuse there.

**bradulet** · 06-03-2013, 06:31 PM

Thanks Marvin,
I appreciate you sending a solution promptly. It does seem to work and VLOOKUP was on my mind when I had to deal with this. The only problem I have with this approach is that, from what I know, in order for it to work properly one has to sort the file alphabetically by the variable that is being looked at.
Also, when matching by SSN fails, I need a combination of "first 3 letters FirstName + first 3 letters LastName + DOB" for it to be considered valid (we're talking about thousands of records where there are duplicates of John Smith and the only differentiating factor is DOB).
Should I understand that this isn't one of those "one stop shop"?
Thanks again.
Bradulet

**MarvinP** · 06-03-2013, 06:48 PM

Hi bradulet,

If you use the "False" argument at the end of the VLookup, you don't need to have things sorted alphabetically. I don't understand the "one stop shop" reference.

If you have lots of data there is likely two people having the same birthdate and first three letters of first or last names (or both). What do you expect to happen then? Big data sets have their problems.

In N2 try this for a helper formula

=LEFT(B2,3)&LEFT(A2,3)&D2

Then do a VLookup on it like the others on Sheet2.

**bradulet** · 06-03-2013, 07:26 PM

Hi Marvin,
This new helper formula works nicely too. Thank you.
Now, how can I get the rest of the matched records moved over into my SecondFile? (that's why I was saying a "one stop shop" ...a program to do it all in one step)
I appreciate your patience with this.
Bradulet