Hello
Thank you for taking the time to check out my question.
I searched the forum for an answer and I have found things that are in the same area, but not quite what I am looking to do.
I have two sample sheets: source and location
source
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 121px"><col width="177"><col width="171"><col width="100"><col width="100"><col width="100"><col width="198"><col width="178"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Worker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Work Email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Current Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Current City[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Worker Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Effective Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Old Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]New Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Old State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]New State[/TD]
[/TR]
[TR]
[TD]Genevieve Jimenez[/TD]
[TD]g.j@co.com[/TD]
[TD]40123 New Street[/TD]
[TD]Los Angeles[/TD]
[TD]Contingent Worker[/TD]
[TD="align: right"]2/27/17[/TD]
[TD]2101 Old Street[/TD]
[TD]40123 New Street[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Pam Banks[/TD]
[TD]p.b@co.com[/TD]
[TD]41456 Update Avenue[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]12/28/17[/TD]
[TD]2202 Last Drive[/TD]
[TD]41456 Update Avenue[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Tasha Bishop[/TD]
[TD]t.b@co.com[/TD]
[TD]42789 Newish Circle[/TD]
[TD]Seattle[/TD]
[TD]Employee[/TD]
[TD="align: right"]8/17/18[/TD]
[TD]2303 Ancient Avenue[/TD]
[TD]42789 Newish Circle[/TD]
[TD]Washington[/TD]
[TD]Washington[/TD]
[/TR]
[TR]
[TD]David Bishop[/TD]
[TD]d.b@co.com[/TD]
[TD]431011 Newton Lane[/TD]
[TD]New York[/TD]
[TD]Employee[/TD]
[TD="align: right"]1/22/18[/TD]
[TD]2404 Oldton Circle[/TD]
[TD]431011 Newton Lane[/TD]
[TD]New York[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Monica Elliott[/TD]
[TD]m.e@co.com[/TD]
[TD]441213 Latest Avenue[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]12/28/17[/TD]
[TD]2505 Previous Lane[/TD]
[TD]441213 Latest Avenue[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]David Jefferson[/TD]
[TD]d.j@co.com[/TD]
[TD]451415 Brand New Drive[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]7/17/17[/TD]
[TD]2606 Old School Street[/TD]
[TD]451415 Brand New Drive[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Lori Mcbride[/TD]
[TD]l.m@co.com[/TD]
[TD]461617 New School Avenue[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]7/31/17[/TD]
[TD]2707 Historic Pass[/TD]
[TD]461617 New School Avenue[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Carolyn Rhodes[/TD]
[TD]c.r@co.com[/TD]
[TD]471819 Current Lane[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]3/19/18[/TD]
[TD]2808 Outdate Circle[/TD]
[TD]471819 Current Lane[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Ted Wood[/TD]
[TD]t.w@co.com[/TD]
[TD]482021 Now Drive[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]4/1/17[/TD]
[TD]2909 Last Avenue[/TD]
[TD]482021 Now Drive[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Kenneth Franklin[/TD]
[TD]k.f@co.com[/TD]
[TD]492223 Recent Circle[/TD]
[TD]New York[/TD]
[TD]Contingent Worker[/TD]
[TD="align: right"]7/31/17[/TD]
[TD]3001 History Lane[/TD]
[TD]492223 Recent Circle[/TD]
[TD]New York[/TD]
[TD]New York[/TD]
[/TR]
</tbody>[/TABLE]
location
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="143"><col width="100"><col width="179"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Issue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Project[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Reporter[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Created Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Updated Location[/TD]
[/TR]
[TR]
[TD]task-1011[/TD]
[TD]IT[/TD]
[TD]Genevieve Jimenez[/TD]
[TD="align: right"]1/29/18[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1213[/TD]
[TD]ENG[/TD]
[TD]Pam Banks[/TD]
[TD="align: right"]2/6/18[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1415[/TD]
[TD]IT[/TD]
[TD]Tasha Bishop[/TD]
[TD="align: right"]3/13/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1617[/TD]
[TD]ENG[/TD]
[TD]David Bishop[/TD]
[TD="align: right"]4/10/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1819[/TD]
[TD]IT[/TD]
[TD]Monica Elliott[/TD]
[TD="align: right"]4/19/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2021[/TD]
[TD]ENG[/TD]
[TD]David Jefferson[/TD]
[TD="align: right"]4/20/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2223[/TD]
[TD]IT[/TD]
[TD]Lori Mcbride[/TD]
[TD="align: right"]1/7/19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2425[/TD]
[TD]ENG[/TD]
[TD]Carolyn Rhodes[/TD]
[TD="align: right"]6/20/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2627[/TD]
[TD]IT[/TD]
[TD]Ted Wood[/TD]
[TD="align: right"]1/2/19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2829[/TD]
[TD]ENG[/TD]
[TD]Kenneth Franklin[/TD]
[TD="align: right"]10/10/17[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
On the location sheet in the "Updated Location" column I am trying to determine the location of an individual based on the closest but not over "Effective Date" on the source sheet.
It feels like two functions and I have been able to get close, but not quite to what feels like the right answer.
Using
I am able to find partial matches based on the names in source!C:C.
That returns the first match.
I am hoping to get a reduced scope of partial matches that would then compare dates and find the closest but not over match.
Something along the line of
I feel I may have to do another scope reduction to closer to a match before I compare dates.
Just tossing some pseudo code to better explain myself:
Select source:"New Location" where source:worker is like "Bishop" and where source worker is like "Tasha" and where source:"Effective Date" is equal to or less than Location:"Created Date" Else select source:"Old Location"
In the end I would like the column location:"Updated Location" to be populated with the date driven, name matching result.
Any thoughts on this or different approaches would be welcomed.
Thank you for taking the time to check out my question.
I searched the forum for an answer and I have found things that are in the same area, but not quite what I am looking to do.
I have two sample sheets: source and location
source
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 121px"><col width="177"><col width="171"><col width="100"><col width="100"><col width="100"><col width="198"><col width="178"><col width="100"><col width="100"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Worker[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Work Email[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Current Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Current City[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Worker Type[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Effective Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Old Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]New Location[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Old State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]New State[/TD]
[/TR]
[TR]
[TD]Genevieve Jimenez[/TD]
[TD]g.j@co.com[/TD]
[TD]40123 New Street[/TD]
[TD]Los Angeles[/TD]
[TD]Contingent Worker[/TD]
[TD="align: right"]2/27/17[/TD]
[TD]2101 Old Street[/TD]
[TD]40123 New Street[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Pam Banks[/TD]
[TD]p.b@co.com[/TD]
[TD]41456 Update Avenue[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]12/28/17[/TD]
[TD]2202 Last Drive[/TD]
[TD]41456 Update Avenue[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Tasha Bishop[/TD]
[TD]t.b@co.com[/TD]
[TD]42789 Newish Circle[/TD]
[TD]Seattle[/TD]
[TD]Employee[/TD]
[TD="align: right"]8/17/18[/TD]
[TD]2303 Ancient Avenue[/TD]
[TD]42789 Newish Circle[/TD]
[TD]Washington[/TD]
[TD]Washington[/TD]
[/TR]
[TR]
[TD]David Bishop[/TD]
[TD]d.b@co.com[/TD]
[TD]431011 Newton Lane[/TD]
[TD]New York[/TD]
[TD]Employee[/TD]
[TD="align: right"]1/22/18[/TD]
[TD]2404 Oldton Circle[/TD]
[TD]431011 Newton Lane[/TD]
[TD]New York[/TD]
[TD]New York[/TD]
[/TR]
[TR]
[TD]Monica Elliott[/TD]
[TD]m.e@co.com[/TD]
[TD]441213 Latest Avenue[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]12/28/17[/TD]
[TD]2505 Previous Lane[/TD]
[TD]441213 Latest Avenue[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]David Jefferson[/TD]
[TD]d.j@co.com[/TD]
[TD]451415 Brand New Drive[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]7/17/17[/TD]
[TD]2606 Old School Street[/TD]
[TD]451415 Brand New Drive[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Lori Mcbride[/TD]
[TD]l.m@co.com[/TD]
[TD]461617 New School Avenue[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]7/31/17[/TD]
[TD]2707 Historic Pass[/TD]
[TD]461617 New School Avenue[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Carolyn Rhodes[/TD]
[TD]c.r@co.com[/TD]
[TD]471819 Current Lane[/TD]
[TD]Austin[/TD]
[TD]Employee[/TD]
[TD="align: right"]3/19/18[/TD]
[TD]2808 Outdate Circle[/TD]
[TD]471819 Current Lane[/TD]
[TD]Texas[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Ted Wood[/TD]
[TD]t.w@co.com[/TD]
[TD]482021 Now Drive[/TD]
[TD]Los Angeles[/TD]
[TD]Employee[/TD]
[TD="align: right"]4/1/17[/TD]
[TD]2909 Last Avenue[/TD]
[TD]482021 Now Drive[/TD]
[TD]California[/TD]
[TD]California[/TD]
[/TR]
[TR]
[TD]Kenneth Franklin[/TD]
[TD]k.f@co.com[/TD]
[TD]492223 Recent Circle[/TD]
[TD]New York[/TD]
[TD]Contingent Worker[/TD]
[TD="align: right"]7/31/17[/TD]
[TD]3001 History Lane[/TD]
[TD]492223 Recent Circle[/TD]
[TD]New York[/TD]
[TD]New York[/TD]
[/TR]
</tbody>[/TABLE]
location
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 100px"><col width="100"><col width="143"><col width="100"><col width="179"></colgroup><tbody>[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Issue[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Project[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Reporter[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Created Date[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Updated Location[/TD]
[/TR]
[TR]
[TD]task-1011[/TD]
[TD]IT[/TD]
[TD]Genevieve Jimenez[/TD]
[TD="align: right"]1/29/18[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1213[/TD]
[TD]ENG[/TD]
[TD]Pam Banks[/TD]
[TD="align: right"]2/6/18[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1415[/TD]
[TD]IT[/TD]
[TD]Tasha Bishop[/TD]
[TD="align: right"]3/13/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1617[/TD]
[TD]ENG[/TD]
[TD]David Bishop[/TD]
[TD="align: right"]4/10/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-1819[/TD]
[TD]IT[/TD]
[TD]Monica Elliott[/TD]
[TD="align: right"]4/19/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2021[/TD]
[TD]ENG[/TD]
[TD]David Jefferson[/TD]
[TD="align: right"]4/20/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2223[/TD]
[TD]IT[/TD]
[TD]Lori Mcbride[/TD]
[TD="align: right"]1/7/19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2425[/TD]
[TD]ENG[/TD]
[TD]Carolyn Rhodes[/TD]
[TD="align: right"]6/20/17[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2627[/TD]
[TD]IT[/TD]
[TD]Ted Wood[/TD]
[TD="align: right"]1/2/19[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD]task-2829[/TD]
[TD]ENG[/TD]
[TD]Kenneth Franklin[/TD]
[TD="align: right"]10/10/17[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
On the location sheet in the "Updated Location" column I am trying to determine the location of an individual based on the closest but not over "Effective Date" on the source sheet.
It feels like two functions and I have been able to get close, but not quite to what feels like the right answer.
Using
Code:
[COLOR=#000000][FONT=Inconsolata]=[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]INDEX[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=#F7981D][FONT=Inconsolata]source!C:C[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]MATCH[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]([/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=#7E3794][FONT=Inconsolata]C3[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata]&[/FONT][/COLOR][COLOR=green][FONT=Inconsolata]"*"[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#11A9CC][FONT=Inconsolata]source!$A:$A[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata],[/FONT][/COLOR][COLOR=#1155CC][FONT=Inconsolata]0[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR][COLOR=#000000][FONT=Inconsolata])[/FONT][/COLOR]
That returns the first match.
I am hoping to get a reduced scope of partial matches that would then compare dates and find the closest but not over match.
Something along the line of
Code:
E2>=source!F2,source!G2,source!H2
I feel I may have to do another scope reduction to closer to a match before I compare dates.
Just tossing some pseudo code to better explain myself:
Select source:"New Location" where source:worker is like "Bishop" and where source worker is like "Tasha" and where source:"Effective Date" is equal to or less than Location:"Created Date" Else select source:"Old Location"
In the end I would like the column location:"Updated Location" to be populated with the date driven, name matching result.
Any thoughts on this or different approaches would be welcomed.