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

[TABLE="class: grid, width: 50"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD][/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]

Result:

[TABLE="width: 50"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]2,3,5[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]3,4,5[/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]1,2,3,5[/TD]
[/TR]
</tbody>[/TABLE]


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.