Return multiple matches with multiple criteria as text

gforgacs

New Member
Joined
Mar 17, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hey, I have read some similar threads here and tried some of them but still no solution for me.

I have a large database where I would like to list all destinations of each driver for certain days. The main challenges are 1. any driver can appear in any of 3 columns, 2. there can be more than one matches. I have managed to use index; match formulas with IFNA logic to solve the first challenge, but cannot solve the second one to concat the multiple matches as it would work in a pivot table. Pivot table is not an option becase of the first challenge. Below is an example.

Data is in Sheet 1, expected result in Sheet 2 B2:C5


Sheet 1ABCDESheet 2ABC
1​
DateDriver 1Driver 2Driver 3Destination
1​
2021.03.17​
2021.03.20​
2​
2021.03.17​
Driver ABarcelona
2​
Driver ABarcelonaBarcelona, Prague
3​
2021.03.17​
Driver BDriver DWien
3​
Driver BWienWien
4​
2021.03.17​
Driver CBudapest
4​
Driver CBudapestBudapest, Prague
5​
2021.03.20​
Driver ABarcelona
5​
Driver DWienWien
6​
2021.03.20​
Driver BDriver DWien
7​
2021.03.20​
Driver CBudapest
8​
2021.03.20​
Driver ADriver CPrague
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DateDriver 1Driver 2Driver 3Destination17/03/202120/03/2021
217/03/2021Driver ABarcelonaDriver ABarcelonaBarcelona, Prague
317/03/2021Driver BDriver DWienDriver BWienWien
417/03/2021Driver CBudapestDriver CBudapestBudapest, Prague
520/03/2021Driver ABarcelonaDriver DWienWien
620/03/2021Driver BDriver DWienDriver E  
720/03/2021Driver CBudapest
820/03/2021Driver ADriver CPrague
Sheet1
Cell Formulas
RangeFormula
I2:J6I2=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DateDriver 1Driver 2Driver 3Destination17/03/202120/03/2021
217/03/2021Driver ABarcelonaDriver ABarcelonaBarcelona, Prague
317/03/2021Driver BDriver DWienDriver BWienWien
417/03/2021Driver CBudapestDriver CBudapestBudapest, Prague
520/03/2021Driver ABarcelonaDriver DWienWien
620/03/2021Driver BDriver DWienDriver E  
720/03/2021Driver CBudapest
820/03/2021Driver ADriver CPrague
Sheet1
Cell Formulas
RangeFormula
I2:J6I2=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))
WOW, Hats off! It works. =SZÖVEGÖSSZEFŰZÉS(", ";IGAZ;SZŰRŐ(Túranapló_heti!$H:$H;(Túranapló_heti!$D:$D='Sofőr check'!S$4)*((Túranapló_heti!$K:$K='Sofőr check'!$B14)+(Túranapló_heti!$L:$L='Sofőr check'!$B14)+(Túranapló_heti!$M:$M='Sofőr check'!$B14));""))

What if I would like to add another criteria, let's say, only filter if value in column F is "OK" or empty?

Sheet 1ABCDEFSheet 2ABC
1​
DateDriver 1Driver 2Driver 3DestinationStatus
1​
2021.03.17​
2021.03.20​
2​
2021.03.17​
Driver ABarcelonaOK
2​
Driver ABarcelonaBarcelona, Prague
3​
2021.03.17​
Driver BDriver DWienOK
3​
Driver BWienWien
4​
2021.03.17​
Driver CBudapest
4​
Driver CBudapestBudapest, Prague
5​
2021.03.20​
Driver ABarcelonaNA
5​
Driver DWienWien
6​
2021.03.20​
Driver BDriver DWienOK
7​
2021.03.20​
Driver CBudapestOK
8​
2021.03.20​
Driver ADriver CPrague
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DateDriver 1Driver 2Driver 3DestinationStatus17/03/202120/03/2021
217/03/2021Driver ABarcelonaOKDriver ABarcelona 
317/03/2021Driver BDriver DWienOKDriver BWienWien
417/03/2021Driver CBudapestDriver C Budapest
520/03/2021Driver ABarcelonaNADriver DWienWien
620/03/2021Driver BDriver DWienOKDriver E  
720/03/2021Driver CBudapestOK
820/03/2021Driver ADriver CPrague
Sheet1
Cell Formulas
RangeFormula
I2:J6I2=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*($F$2:$F$8="OK")*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJ
1DateDriver 1Driver 2Driver 3DestinationStatus17/03/202120/03/2021
217/03/2021Driver ABarcelonaOKDriver ABarcelona 
317/03/2021Driver BDriver DWienOKDriver BWienWien
417/03/2021Driver CBudapestDriver C Budapest
520/03/2021Driver ABarcelonaNADriver DWienWien
620/03/2021Driver BDriver DWienOKDriver E  
720/03/2021Driver CBudapestOK
820/03/2021Driver ADriver CPrague
Sheet1
Cell Formulas
RangeFormula
I2:J6I2=TEXTJOIN(", ",,FILTER($E$2:$E$8,($A$2:$A$8=I$1)*($F$2:$F$8="OK")*(($B$2:$B$8=$H2)+($C$2:$C$8=$H2)+($D$2:$D$8=$H2)),""))
Yep, yet again perfect, I think I've got the hinge of it. Thanks a lot!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

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