Concat based on a "trigger" cell

[nickhills1]

Morning Excel Champions..

Looking for your assistance please.

I am trying to work out how to Concat cells based on another "trigger" cell.

So in my example, i want to Concat the all of the job titles together, split with a comma, for each of the trigger numbers in column B.

For example all of the job titles with "1" in column B would concat from C2, C3 and C4 into E2

All of the job titles for "3" would concat from C6 and C7

Thanks in advance

Nick

 

[Alex Blakenburg]

Do you just mean something like this ?

20240619 Filter TextJoin nickhills1.xlsx
	A	B	C	D	E
1		Trigger	Job Title		Result
2		1	Doctor		Doctor, Nurse, Teacher
3		1	Nurse
4		1	Teacher
5		2	Doctor		Doctor
6		3	Cleaner		Cleaner, Driver
7		3	Driver
8		4	Project Manager		Project Manager, Excel Guru, Farmer, Cleaner, Driver
9		4	Excel Guru
10		4	Farmer
11		4	Cleaner
12		4	Driver
Data

Cell Formulas
Range	Formula
E2:E12	E2	=IF(B2<>B1,TEXTJOIN(", ",TRUE,FILTER($C$2:$C$12,$B$2:$B$12=B2)),"")

 

[nickhills1]

Do you just mean something like this ?

20240619 Filter TextJoin nickhills1.xlsx
	A	B	C	D	E
1		Trigger	Job Title		Result
2		1	Doctor		Doctor, Nurse, Teacher
3		1	Nurse
4		1	Teacher
5		2	Doctor		Doctor
6		3	Cleaner		Cleaner, Driver
7		3	Driver
8		4	Project Manager		Project Manager, Excel Guru, Farmer, Cleaner, Driver
9		4	Excel Guru
10		4	Farmer
11		4	Cleaner
12		4	Driver
Data

Cell Formulas
Range	Formula
E2:E12	E2	=IF(B2<>B1,TEXTJOIN(", ",TRUE,FILTER($C$2:$C$12,$B$2:$B$12=B2)),"")

Many thanks Alex.. That seems to work perfectly!!!

Have a fantastic Thursday

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.