Need a formula to return header if cell is not blank and concatenate the header names separated by comma

[bb19august]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Car[/TD]
[TD]Bus[/TD]
[TD]Train[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Car, Bus[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]Bus, Train[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]No[/TD]
[TD]Car, Train[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Car, Bus[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD]Car, Train[/TD]
[/TR]
</tbody>[/TABLE]

Request your help to have a formula in excel to get a result as shown above. Cells could have numbers or blanks or text. if there is any number/text in any cell(s)the header(s) has to copied and concatenated and separated by comma.

 

[Yongle]

In D2 copied down
=TRIM(SUBSTITUTE(IF(A2<>"",A$1,",")&IF(B2<>"",", "& B$1,"")&IF(C2<>"",", "&C$1," "),",,",""))

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Car[/td][td]Bus[/td][td]Train[/td][td]Result[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td][/td][td]Car, Bus[/td][td]=TRIM(SUBSTITUTE(IF(A2<>"",A$1,",")&IF(B2<>"",", "& B$1,"")&IF(C2<>"",", "&C$1," "),",,",""))[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td][/td][td]

2​

[/td][td]

2​

[/td][td]Bus, Train[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]

1​

[/td][td][/td][td]No[/td][td]Car, Train[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

7​

[/td][td]

3​

[/td][td][/td][td]Car, Bus[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]

10​

[/td][td][/td][td]Yes[/td][td]Car, Train[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td][/td][td]Yes[/td][td]Train[/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[bb19august]

It worked as expected. Thank you so much for your time and help, Yongle.

 

[Yongle]

thank you for the fedback