Forum Discussion
If/Then Assistance
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!
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!