Return the value of a header row if cell contains data

[DctheDC]

Hi all

I have a list of names in a column A and a list of categories Cols B-F

Each person can be tagged to several categories with the value of either 0 1 2 3 or 4

I am after a way to create a separate summary sheet that will tell me the person’s name in one cell (which I can do with =Sheet1!A2 ) and a string of text delimited by commas showing all the categories they have been tagged too regardless of the value used.

Example Table

A	B	C	D	E	F
	1	2	3	4	5
Bob		0	3		2
Peter			3	4	0
Frank	4	2	0		3

<tbody>
</tbody>

Result:

A	B
Bob	2,3,5
Peter	3,4,5
Frank	1,2,3,5

<tbody>
</tbody>

My full list of data has over 150 people and 160 different categories

Any Ideas?

Thanks

 

[Aladin Akyurek]

Thanks Aladin,

That's perfect and does what I want

Thanks for your help!

You are welcome. Thanks for the update.