ThePangloss
New Member
- Joined
- Jun 19, 2015
- Messages
- 40
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 Imgur
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.
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 Imgur
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.
