Return cell headers from values in cell of multiple column

[Miratshah]

Hi,

I have few columns in which either there is Yes or no as values. I need a formula to return all column headers where there is yes separated by comma. I have more than 50000 records and have to do this on every other day. Is there a way to achieve this through a formula? Here is an example:-

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Required result[/TD]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Header 4[/TD]
[TD]Header 5[/TD]
[/TR]
[TR]
[TD]Header 1, Header 3, Header 4[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]Header 2, Header 4, Header 5[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]N[/TD]
[TD]Y[/TD]
[TD]Y[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance

 

[bosco_yip]

Re: Return cell headerS from values in cell of multiple column

1] Assume your data put in A1:F3 with header

2] In B2, copied across to F2 and all copied down :

=IF(ISNUMBER(FIND(B$1,$A2)),"Y","N")

Regards
Bosco

 

[Miratshah]

Re: Return cell headerS from values in cell of multiple column

Hi Bosco_yip,

I guess i did not do a good job in explaining the problem.

I need final result in column A. Final result should be header values of those column cells, where there is Y. Therefore cell A2 as per table in 1st post is the required answer. I.e Header 1, Header 3, Header 4 because "Y" appears in those respective column.

Column "Required result" is I need to achieve through your help.

 

[bosco_yip]

Re: Return cell headerS from values in cell of multiple column

Then,

In A2, copied down :

=SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(IF(B2="Y",B$1,"")&"@"&IF(C2="Y",C$1,"")&"@"&IF(D2="Y",D$1,"")&"@"&IF(E2="Y",E$1,"")&"@"&IF(F2="Y",F$1,"")," ","#"),"@"," "))," ",", "),"#"," ")

Regards
Bosco

 

[Miratshah]

Re: Return cell headerS from values in cell of multiple column

You are an angle sent from heaven. it works just like how I want it. Thank you bosco_yip.