Return multiple results from duplicate name and date range lookup

H

harleythecav

New Member
Joined
May 18, 2020
Messages
7
Office Version
  1. 2016
Platform
  1. 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
Namesome datasome dataStart Datesome datasome datasome datasome datasome datasome data
Name 124/07/2020
Name 127/07/2020
Name 129/07/2020
Name 224/12/2019
Name 221/03/2020
Name 329/07/2020
Name 420/03/2020
Name 420/03/2020
Name 421/03/2020
Name 422/03/2020
Name 422/03/2020
Name 423/03/2020
Name 423/03/2020
Name 424/03/2020
Name 424/03/2020


SHEET 2
some datanamesome datasome datastart dateend datesome datacodeservicesome datasome datasome datasome data
Name 221/02/201908/04/2020code 1service 9
Name 221/02/201908/04/2020code 2service 10
Name 221/02/201908/04/2020code 3service 11
Name 221/02/201908/04/2020code 4service 12
Name 221/02/201908/04/2020code 5service 13
Name 221/02/201908/04/2020code 6service 17
Name 221/02/201908/04/2020code 7service 15
Name 221/02/201908/04/2020code 8service 7
Name 221/02/201908/04/2020code 9service 2
Name 221/02/201908/04/2020code 10service 1
Name 221/02/201908/04/2020code 11service 5
Name 221/02/201908/04/2020code 12service 3
Name 221/02/201908/04/2020code 13service 4
Name 221/02/201908/04/2020code 14service 14
Name 221/02/201908/04/2020code 15service 8
Name 209/04/202009/04/2021code 16service 6
Name 209/04/202009/04/2021code 17service 9
Name 209/04/202009/04/2021code 18service 10
Name 209/04/202009/04/2021code 19service 11
Name 209/04/202009/04/2021code 20service 12
Name 209/04/202009/04/2021code 21service 13
Name 209/04/202009/04/2021code 22service 5
Name 209/04/202009/04/2021code 23service 3
Name 209/04/202009/04/2021code 24service 15
Name 209/04/202009/04/2021code 25service 16
Name 209/04/202009/04/2021code 26service 16
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
Since you are using Excel 2016, you can use either an array formula such as Index/Match to get results or import the ranges into the Data Model and use a DAX formula in PowerPivot. Which do you prefer?
 
Upvote 0
Allright, somewhat complex using standard array formulas. See workbook sample below. Note the following:

1. After using formula, must press CTRL-SHIFT-ENTER to get brackets
2. Formula first entered in cell L2, then copied down the column
3. Since you can only get one result per cell, you then have to drag the formula across the rows to get the multiple hits that match the criteria. You can then use another formula like Concat to consolidate to one cell (Textjoin would be preferred but not sure if available in Excel 2016).

Overall, may be better to use PowerPivot to accomplish this, as you can use one formula to get all results in a single cell.

SampleData.xlsx
 
Upvote 0
Hi brawnystaff
Apologies for not being available to reply to your question - I'd already gone to bed by that time.
Your solution worked great - it's all I needed to get the list of items that way.
I figured it would be a bit messy but I can work with that option to achieve the rest of what I need.
Much appreciated!
 
Upvote 0
Since you stated you also have access to 365 here is another option.
Sheet 1
Book1
ABCDKL
1Namesome datasome dataStart Date
2Name 17/24/2020 9:00:00 AM 
3Name 17/27/2020 9:00:00 AM 
4Name 17/29/2020 9:00:00 AM 
5Name 212/24/2019 5:00:00 PMservice 9, service 10, service 11, service 12, service 13, service 17, service 15, service 7, service 2, service 1, service 5, service 3, service 4, service 14, service 8
6Name 23/21/2020 10:00:00 AMservice 9, service 10, service 11, service 12, service 13, service 17, service 15, service 7, service 2, service 1, service 5, service 3, service 4, service 14, service 8
7Name 37/29/2020 4:00:00 PM 
8Name 43/20/2020 7:00:00 AM 
9Name 43/20/2020 5:00:00 PM 
10Name 43/21/2020 9:00:00 AM 
11Name 43/22/2020 9:00:00 AM 
12Name 43/22/2020 5:00:00 PM 
13Name 43/23/2020 7:00:00 AM 
14Name 43/23/2020 5:30:00 PM 
15Name 43/24/2020 7:00:00 AM 
16Name 43/24/2020 5:30:00 PM 
17Name 24/20/2020service 6, service 9, service 10, service 11, service 12, service 13, service 5, service 3, service 15, service 16
Sheet1
Cell Formulas
RangeFormula
L2:L17L2=TEXTJOIN(", ",1,TRANSPOSE(UNIQUE(FILTER(Sheet2!$I$2:$I$27,(D2>=Sheet2!$E$2:$E$27)*(D2<=Sheet2!$F$2:$F$27)*(A2=Sheet2!$B$2:$B$27),""))))


Sheet 2
Book1
ABCDEFGHI
1some datanamesome datasome datastart dateend datesome datacodeservice
2Name 22/21/20194/8/2020code 1service 9
3Name 22/21/20194/8/2020code 2service 10
4Name 22/21/20194/8/2020code 3service 11
5Name 22/21/20194/8/2020code 4service 12
6Name 22/21/20194/8/2020code 5service 13
7Name 22/21/20194/8/2020code 6service 17
8Name 22/21/20194/8/2020code 7service 15
9Name 22/21/20194/8/2020code 8service 7
10Name 22/21/20194/8/2020code 9service 2
11Name 22/21/20194/8/2020code 10service 1
12Name 22/21/20194/8/2020code 11service 5
13Name 22/21/20194/8/2020code 12service 3
14Name 22/21/20194/8/2020code 13service 4
15Name 22/21/20194/8/2020code 14service 14
16Name 22/21/20194/8/2020code 15service 8
17Name 24/9/20204/9/2021code 16service 6
18Name 24/9/20204/9/2021code 17service 9
19Name 24/9/20204/9/2021code 18service 10
20Name 24/9/20204/9/2021code 19service 11
21Name 24/9/20204/9/2021code 20service 12
22Name 24/9/20204/9/2021code 21service 13
23Name 24/9/20204/9/2021code 22service 5
24Name 24/9/20204/9/2021code 23service 3
25Name 24/9/20204/9/2021code 24service 15
26Name 24/9/20204/9/2021code 25service 16
27Name 24/9/20204/9/2021code 26service 16
Sheet2
 
Upvote 0
The formula I gave above for Excel 365 can be shortened. No need for the TRANSPOSE function when using TEXTJOIN.

=TEXTJOIN(", ",1,UNIQUE(FILTER(Sheet2!$I$2:$I$27,(D2>=Sheet2!$E$2:$E$27)*(D2<=Sheet2!$F$2:$F$27)*(A2=Sheet2!$B$2:$B$27),"")))
 
Upvote 0
Hi AhoyNC
Thanks for providing an alternative option - I have been using 365 recently for the UNIQUE and FILTER options so I will give your solution a try.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Returning 1 or 0 based on multiple dates
Replies
2
Views
349
bebo021999
B
I
  • Question Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
973
Insurance Dave
I
A
  • Solved
Returning name on multiple date arrays
Replies
9
Views
623
archibell
A
S
  • Question Question
List all staff names that worked under a manager on a certain date
Replies
5
Views
403
Fluff
Fluff
M
  • Question Question
Counting specific dates from multiple columns
Replies
2
Views
400
mattbird
M

Forum statistics

Threads
1,224,817
Messages
6,181,149
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top