If/Then Assistance

[connor015]

Hi ladies/gents, I am looking for an if/then command that will look for matching info and, if at least 2 things match, to put the identifier from the old table's person next to the same person in the new table. 

for example: in the new table: IF (first name from table 1 matches a row from table 2) AND (DOB or Email or Alias) THEN (put List id in #row)

short summary of my goal: I'm working across two databases and trying to find people that might have had their data input incorrectly/have gotten married and therefore their last name has changed. I need at least two data points to match in the old database to the new database and then to copy the identifier from the old database into a row of the new database. Not only do I not know what kind of code would be best BUT there is a possibility we will have more than one individual with say, the same DOB. So if possible, I would need to add (if more than one, [identifier 1]; [identifier 2]) so I can go back and review. I have one table with about 500 names I'm having to manually search an older table with over 9000 entries 4 times per name on average (first/last in 1 match any in 2?; first/dob in 1 match any in 2; first/email in 1 match any in 2, etc. IF yes, copy id...) and it's GRUELING. Any pointers and you will be my hero.

any videos or links to complex if/then commands or other users who have requested something similar would be greatly appreciated as well!

[#2 · Accepted solution]

@connor015 

Based on your question, I have written two bits of VBA code that probably need tweaking to fit your specific worksheet layout. You'll find the code in the attached workbook. I created some sample data to demonstrate how it works. It contains a number of different persons, several called "John", but two of them are born on the same date. There is also a "Jenny" who changed name and mail address and an entirely new person which will, thus, not be found in the "Old" data.

When you match records based on "First name" AND one of "DOB", "Mail" or "Alias", you will not be able to determine which John (with the same birthdate) is which. Therefore, I suggest that you match records based on "First name" AND "DOB" AND one of "Mail" or "Alias". That's in the first bit of code. The other bit of code works on the basis of your original request.

You can press the buttons in the sheet "New" and see what happens. Pressing the lower button will result in both Johns born the same date to point at the last one found in the "Old" data. The top button, however, will separate them correctly.

Look into the code and see if you can get it to work. Maybe, you get inspired to do something different. Perhaps a VBA expert can refine/improve my code, but this is all I could think of for now. Hope it helps!