harleythecav
New Member
- Joined
- May 18, 2020
- Messages
- 7
- Office Version
- 2016
- Platform
- Windows
Hello Forum
I am needing some assistance bringing back multiple results into one cell if a match is found based on a name match and a date falling within a range on another sheet.
Sheet 1: this is my results sheet
Column A = list of names
Column D = start date
Sheet 2: this is my lookup sheet
Column B = list of names
Column E = start date
Column F = end date
Column I = service (this is what will have multiple results that i need returned based on matches found)
Column H = code (once column I is returned, I will need to look up this value in sheet)
The aim is to find any Name match from sheet 1 in sheet 2 along with the start date from sheet 1 to be within the date range (columns E and F) of the matching row in sheet 2...and then to bring back any matching Services (from column I) of which there will be multiple. There are duplicates Names with duplicate dates on sheet 1. There are duplicate Names and date ranges on sheet 2 and obviously multiple (but unique) Service matches - there's 169 possible unique Services.
Each sheet is run from a different report so size of list of names will always vary but shouldn't ever go past about 7500 lines.
I do not have a preference for if multiple matches come back into 1 cell with a delimiter or if they are in separate cells next to each other (I can work with that afterwards) I just need to all possible results returned. Also, if there are duplicate Service matches as part of the multiple results returned for the same Name and date, a unique list is preferred however, again, I'm happy to work that out afterwards once all results are returned.
I am open to VBA suggestions if a formula solution is not possible.
Using Excel 2016 however also have access to 365.
Thank you!
SHEET 1
SHEET 2
I am needing some assistance bringing back multiple results into one cell if a match is found based on a name match and a date falling within a range on another sheet.
Sheet 1: this is my results sheet
Column A = list of names
Column D = start date
Sheet 2: this is my lookup sheet
Column B = list of names
Column E = start date
Column F = end date
Column I = service (this is what will have multiple results that i need returned based on matches found)
Column H = code (once column I is returned, I will need to look up this value in sheet)
The aim is to find any Name match from sheet 1 in sheet 2 along with the start date from sheet 1 to be within the date range (columns E and F) of the matching row in sheet 2...and then to bring back any matching Services (from column I) of which there will be multiple. There are duplicates Names with duplicate dates on sheet 1. There are duplicate Names and date ranges on sheet 2 and obviously multiple (but unique) Service matches - there's 169 possible unique Services.
Each sheet is run from a different report so size of list of names will always vary but shouldn't ever go past about 7500 lines.
I do not have a preference for if multiple matches come back into 1 cell with a delimiter or if they are in separate cells next to each other (I can work with that afterwards) I just need to all possible results returned. Also, if there are duplicate Service matches as part of the multiple results returned for the same Name and date, a unique list is preferred however, again, I'm happy to work that out afterwards once all results are returned.
I am open to VBA suggestions if a formula solution is not possible.
Using Excel 2016 however also have access to 365.
Thank you!
SHEET 1
Name | some data | some data | Start Date | some data | some data | some data | some data | some data | some data |
Name 1 | 24/07/2020 | ||||||||
Name 1 | 27/07/2020 | ||||||||
Name 1 | 29/07/2020 | ||||||||
Name 2 | 24/12/2019 | ||||||||
Name 2 | 21/03/2020 | ||||||||
Name 3 | 29/07/2020 | ||||||||
Name 4 | 20/03/2020 | ||||||||
Name 4 | 20/03/2020 | ||||||||
Name 4 | 21/03/2020 | ||||||||
Name 4 | 22/03/2020 | ||||||||
Name 4 | 22/03/2020 | ||||||||
Name 4 | 23/03/2020 | ||||||||
Name 4 | 23/03/2020 | ||||||||
Name 4 | 24/03/2020 | ||||||||
Name 4 | 24/03/2020 | ||||||||
SHEET 2
some data | name | some data | some data | start date | end date | some data | code | service | some data | some data | some data | some data |
Name 2 | 21/02/2019 | 08/04/2020 | code 1 | service 9 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 2 | service 10 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 3 | service 11 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 4 | service 12 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 5 | service 13 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 6 | service 17 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 7 | service 15 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 8 | service 7 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 9 | service 2 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 10 | service 1 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 11 | service 5 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 12 | service 3 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 13 | service 4 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 14 | service 14 | ||||||||
Name 2 | 21/02/2019 | 08/04/2020 | code 15 | service 8 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 16 | service 6 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 17 | service 9 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 18 | service 10 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 19 | service 11 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 20 | service 12 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 21 | service 13 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 22 | service 5 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 23 | service 3 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 24 | service 15 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 25 | service 16 | ||||||||
Name 2 | 09/04/2020 | 09/04/2021 | code 26 | service 16 | ||||||||