Table to lists

[JackDanIce]

Hi,

Workfile KS v5.9.xlsb
	B	C	D	E	F	G	H	I	J	K
2	Ref Table						Output
3
4		CEO	IT Director	Sales	Intern		A	B	C	D
5	A	1	1	1	0		CEO, IT Director, Sales	CEO	CEO, IT Director	CEO, IT Director, Sales, Intern
6	B	1	0	0	0
7	C	1	1	0	0
8	D	1	1	1	1
Sheet1

I have a cross-tab reference table sheet Data with projects listed vertically, job titles horizontally and matrix for yes and no (1 and 0)

In sheet Output, I have the same projects listed horizontally and underneath each project, I need to list the job titles based on the reference table.

What formula could I use to return this output?

TIA,
Jack

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Ref Table						Output
2
3		CEO	IT Director	Sales	Intern		A	B	C	D
4	A	1	1	1	0		CEO, IT Director, Sales	CEO	CEO, IT Director	CEO, IT Director, Sales, Intern
5	B	1	0	0	0
6	C	1	1	0	0
7	D	1	1	1	1
Shipment

Cell Formulas
Range	Formula
G4:J4	G4	=TEXTJOIN(", ",1,FILTER($B$3:$E$3,FILTER($B$4:$E$7,$A$4:$A$7=G3)=1))

 

[jasonb75]

Maybe

=TEXTJOIN(", ",1,IF(INDEX($C$5:$F$8,MATCH(H4,$B$5:$B$8,0),0),$C$4:$F$4,""))

 

[JackDanIce]

Thank you @Fluff I got as far as TEXTJOIN(",",1,FILTER(...)) but wasn't sure how to make the second part of the filter 'dynamic' to scan the projects vertically to then find the correct row - second FILTER function.

Thank you @jasonb75 I got as far as above then switched to INDEX but still didn't quite complete it.

Much appreciated and can adapt, thank you both!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[JackDanIce]

@Fluff, looks like you can drop "=1" from your formula as well, i.e.

=TEXTJOIN(", ",1,FILTER($B$3:$E$3,FILTER($B$4:$E$7,$A$4:$A$7=G3)))

 

[Fluff]

If you will only ever have 1s & 0s, then as you say the =1 is not required. I tend to put it in as I feel it's more intuitive.

 

[JackDanIce]

Yeah, in this case, it is binary 1 and 0 only (Is person of job title is required in that project or not?), but see I why you'd use, especially when not 1 and 0, thanks for explaining