How to return column heading if there is a value in the row

[sousouris]

Hi,

I have a table. In the column A is a list of all classes available, in row 1 across the top of the table is a list of people. There is an 'x' in the appropriate cell to indicate which classes each person is taking.

Sample table:

	A	B	C	D	E
1		All attendees	Person 1	Person 2	Person 3
2	Class 1	[BLANK]	x		x
3	Class 2	[BLANK]	x	x	x
4	Class 3	[BLANK]		x	x

<TBODY>
</TBODY>

Desired output:
I would like to put a formula in column B that lists all of the people per class.

I suspect I have to use a combintation of CONCATENATE and INDEX MATCH, but I'm less familiar with using INDEX MATCH.

	A	B	C	D	E
1		All attendees	Person 1	Person 2	Person 3
2	Class 1	Person 1, Person 3	x		x
3	Class 2	Person 1, Person 2	x	x
4	Class 3	Person 2, Person 3		x	x

<TBODY>
</TBODY>

Can anyone help?


Thanks!

Sousouris

 

[tigeravatar]

Here is a non-VBA/UDF solution. In cell B2 and copied down use this formula:
=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(IF(C2="x",C$1&"|","")&IF(D2="x",D$1&"|","")&IF(E2="x",E$1&"|","")," ","_"),"|"," "))," ",", "),"_"," ")


Add more &IF(E2="x".... statements as needed, just update the column letter for each one you add. If you'd rather have a VBA/UDF solution, let me know