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

 

[JEC]

Which Excel version do you have? Could you update your profile?

For office 365:

=TEXTJOIN(" - ",TRUE,FILTER($C$2:$D$7,($B$2:$B$7=$G$1)*($A$2:$A$7=F4)))

 

[nuficek]

Which Excel version do you have? Could you update your profile?

For office 365:

=TEXTJOIN(" - ",TRUE,FILTER($C$2:$D$7,($B$2:$B$7=$G$1)*($A$2:$A$7=F4)))

Thanks a lot for your quick reply. I have Office 365. Your formula works good but just for the first date. Is there a way to add automatically next row with the second date as in the example. Btw there is a small mistake in my upload so here is the correct one:

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		16.9.2021	text9 - text10
6	16.9.2021	ID05	text9	text10
7	20.9.2021	ID33	text33	text34
Sheet1

 

[Fluff]

How about

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

Cell Formulas
Range	Formula
F4:F5	F4	=UNIQUE(FILTER(A2:A7,B2:B7=G1))
G4:G5	G4	=TEXTJOIN(" - ",,FILTER($C$2:$D$7,($B$2:$B$7=$G$1)*($A$2:$A$7=F4)))
Dynamic array formulas.

 

[JEC]

Try to run this macro to generate all at once.

Sub jec()
 ar = Sheets(1).Range("A1:D7")
 With CreateObject("scripting.dictionary")
    For i = 2 To UBound(ar)
      If ar(i, 2) = Sheets(1).Range("G1") Then .Item(ar(i, 1)) = .Item(ar(i, 1)) & IIf(.exists(ar(i, 1)), " - ", "") & ar(i, 3) & " - " & ar(i, 4)
    Next
   Sheets(1).Cells(4, 6).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

 

[nuficek]

Try to run this macro to generate all at once.

Sub jec()
 ar = Sheets(1).Range("A1:D7")
 With CreateObject("scripting.dictionary")
    For i = 2 To UBound(ar)
      If ar(i, 2) = Sheets(1).Range("G1") Then .Item(ar(i, 1)) = .Item(ar(i, 1)) & IIf(.exists(ar(i, 1)), " - ", "") & ar(i, 3) & " - " & ar(i, 4)
    Next
   Sheets(1).Cells(4, 6).Resize(.Count, 2) = Application.Transpose(Array(.keys, .items))
 End With
End Sub

Unfortunately VBA code doesn't work for me. But it seems to be the best and easiest solution.

 

[Fluff]

Have you tried the formula I suggested?

 

[nuficek]

Y

Have you tried the formula I suggested?

Yes I tried. But when I change the data for ID01 the second row is wrong:

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

Cell Formulas
Range	Formula
F4:F5	F4	=UNIQUE(FILTER(A2:A7,B2:B7=G1))
G4	G4	=TEXTJOIN(" - ",,FILTER($C$2:$D$7,($B$2:$B$7=$G$1)*($A$2:$A$7=F4)))
Dynamic array formulas.

 

[Fluff]

You need to drag the formula in G4 down.

 

[nuficek]

You need to drag the formula in G4 down.

Ohh, great. Now it works. Thanks a lot. I have learned something new.