Textjoin based on max number of occurrences

[ellison]

Hi, we are trying to join text together based on the number of occurrences...

i.e. to concatenate relevant info into the row which is the first occurrence

Hopefully this may make it a little clearer

textjoin-based-on-max-occurrences-question.xlsx
	A	B	C	D	E
1	Row	Classification	nth occurrence of the class'n	Info	Target
2	2	Alpha	1	Please	Occurrence 1: Please ; Occurrence 2: Amalgamate ; Occurrence 3: This
3	3	Alpha	2	Amalgamate
4	4	Alpha	3	This
5	5	Beta	1	Column	Occurrence 1: Column ; Occurrence 2: ;
6	6	Beta	2
7	7	Charlie	1	Some Fields	Occurrence 1: Some Fields ;
8	8	Delta	1		Occurrence 1: ; Occurrence 2: Are ; Occurrence 3: ; Occurrence 4: Numbers ; Occurrence 5: 10;
9	9	Delta	2	Are
10	10	Delta	3
11	11	Delta	4	Numbers
12	12	Delta	5	10
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=COUNTIF(B$2:B2,B2)

Huge thanks for taking a look!

 

[Peter_SSs]

Your Target results don't look entirely consistent to me. Would this be sufficient?

23 11 09.xlsm
	B		D	E
1	Classification		Info
2	Alpha		Please	Occurrence 1: Please ; Occurrence 2: Amalgamate ; Occurrence 3: This
3	Alpha		Amalgamate
4	Alpha		This
5	Beta		Column	Occurrence 1: Column ; Occurrence 2:
6	Beta
7	Charlie		Some Fields	Occurrence 1: Some Fields
8	Delta			Occurrence 1: ; Occurrence 2: Are ; Occurrence 3: ; Occurrence 4: Numbers ; Occurrence 5: 10
9	Delta		Are
10	Delta
11	Delta		Numbers
12	Delta		10
Textjoin

Cell Formulas
Range	Formula
E2:E12	E2	=IF(B2=B1,"",LET(f,FILTER(D$2:D$12,B$2:B$12=B2),TEXTJOIN(" ; ",,"Occurrence "&SEQUENCE(ROWS(f))&": "&f)))

 

[ellison]

Hi Peter, your results are better than what I'd hoped for ......!

Absolutely brilliant, does the job beautifullly

Many thanks

 

[Peter_SSs]

You're welcome. Glad it helped. Thanks for the follow-up.