Return Multiple Column headings in a single Cell

[qadeerahmed]

Hi,

I've a question with slight variation as it was posted in this thread.

https://www.mrexcel.com/forum/excel-questions/639139-return-multiple-column-headings.html


I need to return the column headings for each matching x in a row; to a single cell instead of separate cells.

Thanks for your time!

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Mark 1	Mark 2	Mark 3	Mark 4	Mark 5	Mark 6	Mark 7	Mark 8	Mark 9	Mark 10
2	1	2	x	x	4	5	10	10	9	1	Mark 3,Mark 4
3	5	x	6	8	6	4	9	8	5	x	Mark 2,Mark 10
Sheet1

In K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF($A2:$J2="x",$A$1:$J$1,""))

 

[qadeerahmed]

Thanks @Aladin Akyurek

thanks for this formula, it solved my problem!

 

[Aladin Akyurek]

Thanks @Aladin Akyurek

thanks for this formula, it solved my problem!

You are welcome. Thanks for the update.

 

[NITIN MANE]

Hi Aladin,

I'm using EXCEL 2013, But when i try this it is not working.
It gives below error "#NAME?"

Plz help me with another formulas.

Thanks


[TABLE="width: 1004"]
<colgroup><col span="9"><col><col></colgroup><tbody>[TR]
[TD]Mark 1[/TD]
[TD]Mark 2[/TD]
[TD]Mark 3[/TD]
[TD]Mark 4[/TD]
[TD]Mark 5[/TD]
[TD]Mark 6[/TD]
[TD]Mark 7[/TD]
[TD]Mark 8[/TD]
[TD]Mark 9[/TD]
[TD]Mark 10[/TD]
[TD]Answer[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]=[/TD]
[TD]TEXTJOIN(",",TRUE,IF($A2:$J2="X",$A$1:$J$1,""))[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]x[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]
[TD]X[/TD]
[TD="align: center"]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]

	A	B	C	D	E	F	G	H	I	J	K
Mark 1	Mark 2	Mark 3	Mark 4	Mark 5	Mark 6	Mark 7	Mark 8	Mark 9	Mark 10
x	x	Mark 3,Mark 4
x	x	Mark 2,Mark 10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]5[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]5[/TD]

</tbody>

Sheet1​

In K2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(",",TRUE,IF($A2:$J2="x",$A$1:$J$1,""))

 

[Aladin Akyurek]

Hi Aladin,

I'm using EXCEL 2013, But when i try this it is not working.
It gives below error "#NAME ?"

Plz help me with another formulas.

Thanks

[…]

Excel 2013 does not have the TEXTJPOIN function, hence the #NAME ? error. Try the following instead...

Add the following VBA code to your workbook as a module, using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Then invoke...

Control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF($A2:$J2="x",","&$A$1:$J$1,"")),1,1,"")