VLOOKUP value and return CONCATENATE text by date in several rows

[nuficek]

Hi,

I have a large table with date, ID and text (several columns). I would like to search value in ID column and return concatenate text. That's not so big deal which I can manage. But as there could be more same ID values for several dates. So would like to find this value and join text together in one row by date. So in the first row there will be the first string matching the date, the second row the second string matching the date ...etc. Is there any chance to do that in Excel?

example.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	ID	DATA 1	DATA 2		SEARCH:	ID05
2	1.9.2021	ID01	text1	text2
3	2.9.2021	ID01	text3	text4		Date	DATA
4	15.9.2021	ID05	text5	text6		15.9.2021	text5 - text6 - text7 -text8
5	15.9.2021	ID05	text7	text8		17.9.2021	text9 - text10
6	16.9.2021	ID05	text9	text10
7	20.9.2021	ID33	text33	text34
Sheet1

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[nuficek]

You need to drag the formula in G4 down.

Hi, thanks a lot for your help and explanation. Just wanna ask one more thing. Is there a way to use the formula with non-adjacent columns? I import data from external source and I need to use just some of the columns. I now it's possible to use help table and copy the columns in the correct coherent order. But I'm just curios if it's possible or it's too complicated and it's better to use the help table.

example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	ID	DATA 1	INFO	DATA 2		SEARCH:	ID05
2	1.9.2021	ID01	text1	aaa	text2
3	2.9.2021	ID01	text3	bbb	text4		Date	DATA
4	15.9.2021	ID05	text5	ccc	text6		15.9.2021	text5 - text6 - text7 -text8
5	15.9.2021	ID05	text7	ddd	text8		16.9.2021	text9 - text10
6	16.9.2021	ID05	text9	eee	text10
7	20.9.2021	ID33	text33	fff	text34
Sheet1

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Date	ID	DATA 1	INFO	DATA 2		SEARCH:	ID05
2	01/09/2021	ID01	text1	aaa	text2
3	02/09/2021	ID01	text3	bbb	text4		Date	DATA
4	15/09/2021	ID05	text5	ccc	text6		15/09/2021	text5 - text6 - text7 - text8
5	15/09/2021	ID05	text7	ddd	text8		16/09/2021	text9 - text10
6	16/09/2021	ID05	text9	eee	text10
7	20/09/2021	ID33	text33	fff	text34
8
9
Lists

Cell Formulas
Range	Formula
G4:G5	G4	=UNIQUE(FILTER(A2:A10,B2:B10=H1))
H4:H5	H4	=TEXTJOIN(" - ",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G4)),LEFT($C$1:$E$1,4)="Data"))
Dynamic array formulas.

 

[nuficek]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Date	ID	DATA 1	INFO	DATA 2		SEARCH:	ID05
2	01/09/2021	ID01	text1	aaa	text2
3	02/09/2021	ID01	text3	bbb	text4		Date	DATA
4	15/09/2021	ID05	text5	ccc	text6		15/09/2021	text5 - text6 - text7 - text8
5	15/09/2021	ID05	text7	ddd	text8		16/09/2021	text9 - text10
6	16/09/2021	ID05	text9	eee	text10
7	20/09/2021	ID33	text33	fff	text34
8
9
Lists

Cell Formulas
Range	Formula
G4:G5	G4	=UNIQUE(FILTER(A2:A10,B2:B10=H1))
H4:H5	H4	=TEXTJOIN(" - ",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G4)),LEFT($C$1:$E$1,4)="Data"))
Dynamic array formulas.

That's great, it works. I understand the most of formula. But could you explain me closer the part LEFT($C$1:$E$1,4)="Data" how this works. I really don't want to just copy what you devise but I would like to learn from it.

 

[nuficek]

That's great, it works. I understand the most of formula. But could you explain me closer the part LEFT($C$1:$E$1,4)="Data" how this works. I really don't want to just copy what you devise but I would like to learn from it.

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1	Date	ID	DATA 1	INFO	DATA 2		SEARCH:	ID05
2	01/09/2021	ID01	text1	aaa	text2
3	02/09/2021	ID01	text3	bbb	text4		Date	DATA
4	15/09/2021	ID05	text5	ccc	text6		15/09/2021	text5 - text6 - text7 - text8
5	15/09/2021	ID05	text7	ddd	text8		16/09/2021	text9 - text10
6	16/09/2021	ID05	text9	eee	text10
7	20/09/2021	ID33	text33	fff	text34
8
9
Lists

Cell Formulas
Range	Formula
G4:G5	G4	=UNIQUE(FILTER(A2:A10,B2:B10=H1))
H4:H5	H4	=TEXTJOIN(" - ",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G4)),LEFT($C$1:$E$1,4)="Data"))
Dynamic array formulas.

I got it. I went deep inside and I figured out. So no need for explanation. Thanks a lot!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[nuficek]

Hi,
I found a limitation of my table. Sometimes there are the same values in columns DATA1 and DATA2 for the same day. Is it possible to eliminate redundancy according the example?

example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date	ID	DATA 1	INFO	DATA 2		SEARCH:	ID05
2	1.9.2021	ID01	text1	aaa	text2
3	2.9.2021	ID01	text3	bbb	text4		Date	DATA
4	15.9.2021	ID05	text5	ccc	text6		15.9.2021	text5 - text6 - text7
5	15.9.2021	ID05	text6	ddd	text7		16.9.2021	text7 - text8
6	16.9.2021	ID05	text7	eee	text8
7	20.9.2021	ID33	text33	fff	text34
Sheet1 (2)

Cell Formulas
Range	Formula
G4:G5	G4	=UNIQUE(FILTER(A2:A10,B2:B10=H1))
H5	H5	=TEXTJOIN(" - ",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G5)),LEFT($C$1:$E$1,4)="Data"))
Dynamic array formulas.

 

[Fluff]

How about

=TEXTJOIN(" - ",,UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G4)),LEFT($C$1:$E$1,4)="Data"))&"</m></k>","//m")))

 

[nuficek]

How about

=TEXTJOIN(" - ",,UNIQUE(FILTERXML("<k><m>"&TEXTJOIN("</m><m>",,FILTER(FILTER($C$2:$E$10,($B$2:$B$10=$H$1)*($A$2:$A$10=G4)),LEFT($C$1:$E$1,4)="Data"))&"</m></k>","//m")))

Thanks a lot for quick reply. That's also totally new function for me so I have to look closer to it and study how it works otherwise it doesn't make sense for me

 

[Fluff]

You're welcome & thanks for the feedback.