VBA to compare a date from one sheet to dates on second sheet of multiple similar people?

[ThePangloss]

I have to match unique ID's using names from one sheet to the other. The problem is the sheet I'm matching to has some people with the same name so when I get the ID it could not be the right one. That's why I need to compare dates of termination and see if they're similar, so I want to go through and compare the date of termination on the first sheet to the multiple dates of termination on the second sheet and see which one matches up closest and then pull that ID from that person. For example if there were 10 John Smith's on the second file, and I'm trying to match the John Smith on my list to his unique # I'd have to look at his date of termination and see which one of those 10 have the closest date of termination(or if it's the exact same)


Anyone with the same names have been marked using SUMPRODUCT so next to their name if the next person has the same name there's a number going from 1 - however many multiples there are. Any people with unique names have no number next to their name, the space would be blank. But I'm only working with the multiples since the uniques were easy enough with lookups
I imagine a macro for this would have to include if num1< num2 (because if two people with duplicate names are next to each other then it would go 1 - 2 (if there are 2 people with the same name) then 1-2-3 (the next has 3 people with the same name) then compare dates and store this then if num2 < num3 then compare dates and store both values and then at the end compare which value has the smallest absolute value then pull the ID for the one with the smallest date which is two columns to the left over.


It looks something like this

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date of Termination[/TD]
[TD]LAST|FIRST[/TD]
[TD]Duplicate or not(if blank then unique)[/TD]
[/TR]
[TR]
[TD]39432[/TD]
[TD]9/27/1963[/TD]
[TD]JOHNSON|MARK[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2929[/TD]
[TD]7/18/1999[/TD]
[TD]JOHNSON|MARK[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]54392[/TD]
[TD]2/3/2010[/TD]
[TD]JOHNSON|MARK[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]62345[/TD]
[TD]5/2/1982[/TD]
[TD]JOHNSON|MARK[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]42902[/TD]
[TD]2/2/2010[/TD]
[TD]SEYMOUR|PHILLIPS[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]384298[/TD]
[TD]4/1/2007[/TD]
[TD]MARLEY|BOB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]384298[/TD]
[TD]6/24/2004[/TD]
[TD]MARLEY|BOB[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]



and

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Date of Termination[/TD]
[TD]LAST|FIRST[/TD]
[TD]Date difference)[/TD]
[/TR]
[TR]
[TD]39432[/TD]
[TD]9/27/2005[/TD]
[TD]MARLEY|BOB[/TD]
[TD]Date in this sheet - Date in previous sheet[/TD]
[/TR]
[TR]
[TD]54392[/TD]
[TD]4/2/2010[/TD]
[TD]JOHNSON|MARK[/TD]
[TD]Date in this sheet - Date in previous sheet[/TD]
[/TR]
[TR]
[TD]42902[/TD]
[TD]2/2/2010[/TD]
[TD]SEYMOUR|PHILLIPS[/TD]
[TD]Date in this sheet - Date in previous sheet[/TD]
[/TR]
</tbody>[/TABLE]


All the duplicates in the first sheet are marked in red and have numbers listed next to them listing how many, the unique names have no numbers next to them. The long number all the way to the left is the ID # they should be matched to, almost all these people have the wrong ID matched to them in the second sheet because when I used a lookup it just took the first ID from the many possible ones that could have duplicates.

I need to make the Column that has "date in this sheet - date inprevious sheet" find the difference of dates that has the smallest absolute value, For example in Mark Johnson's case, the Mark Johnson i'm looking for should have a date of termination near 2010. The only one that matches that is the 3rd Mark Johnson on the main list with ID 54392.

All the people I'm working with right now have duplicates in the other sheet. None are like Seymour Phillips who match up perfectly because of uniqueness.


I'm not sure how to exactly write this though, especially on how to use lookups in a macro and have it go to the dates from the numbers then pull the ID which is one column to the left of the dates.