JacksonPorter
New Member
- Joined
- Jun 19, 2023
- Messages
- 2
- Office Version
- 365
- Platform
- Windows
Greetings Excel Wizards,
I have a bit of a complicated request that I am pursuing, and after butting my head up against Google Searches for the better part of a day, I was hoping I could get some help with my situation. I am a complete Excel noob and prefer to do this if at all possible with formulae instead of VBA, but I'm willing to try anything since, given the size of the task, I cannot do this manually.
I have multiple events each with a specific ID, a separate employee ID, and a date of occurrence on one sheet. On another sheet, each employee ID is associated on different rows with varying dates of service. What I want to find is the closest prior date of service relative to date of occurrence per employee associated with the event. See below:
Sheet 1:
Sheet 2 (dates not given for the rest of the employees; just showing that the amount of dates of service varies from employee to employee):
My thought process went like this: 1) from Sheet 1, VLOOKUP employee ID from event ID (note that employee ID is not unique for each event, thus requiring this step); 2) on Sheet 2, use employee ID to produce a list of their service dates; 3) VLOOKUP date of occurrence from Sheet 1; and 4) from the list of service dates, find the closest prior date of service relative to date of occurrence. In this case, event ID 1 gives employee ID 11111 that has dates 1/2/02, 1/1/03, 1/1/04, and 1/1/05; event ID 1 has 1/2/03 date of occurrence, so formula spits out 1/1/03 as closest employee date of service.
It's really generating the list of dates per employee that's tripped me up since I can't figure out a way to automate it without using concatenation, but then that cannot be used for formulaic comparison to the date of occurrence. Any help is greatly appreciated--thank you all so much.
I have a bit of a complicated request that I am pursuing, and after butting my head up against Google Searches for the better part of a day, I was hoping I could get some help with my situation. I am a complete Excel noob and prefer to do this if at all possible with formulae instead of VBA, but I'm willing to try anything since, given the size of the task, I cannot do this manually.
I have multiple events each with a specific ID, a separate employee ID, and a date of occurrence on one sheet. On another sheet, each employee ID is associated on different rows with varying dates of service. What I want to find is the closest prior date of service relative to date of occurrence per employee associated with the event. See below:
Sheet 1:
Sheet 2 (dates not given for the rest of the employees; just showing that the amount of dates of service varies from employee to employee):
My thought process went like this: 1) from Sheet 1, VLOOKUP employee ID from event ID (note that employee ID is not unique for each event, thus requiring this step); 2) on Sheet 2, use employee ID to produce a list of their service dates; 3) VLOOKUP date of occurrence from Sheet 1; and 4) from the list of service dates, find the closest prior date of service relative to date of occurrence. In this case, event ID 1 gives employee ID 11111 that has dates 1/2/02, 1/1/03, 1/1/04, and 1/1/05; event ID 1 has 1/2/03 date of occurrence, so formula spits out 1/1/03 as closest employee date of service.
It's really generating the list of dates per employee that's tripped me up since I can't figure out a way to automate it without using concatenation, but then that cannot be used for formulaic comparison to the date of occurrence. Any help is greatly appreciated--thank you all so much.
