Retrieving Column Header values in a grid

[mattyblueice]

I have a large client product ID grid I am working with where I am trying to develop a formula that will pick up the column header values (product ID) if an X is present in the cell. The formula is in the Codes column, below is a sample of what I am working with but there are many product codes and clients.

I have researching formulas IF, MATCH and INDEX, but I am stuck about how to get this to work? Can anyone offer any suggestions please?

[TABLE="width: 900"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Client[/TD]
[TD]Codes[/TD]
[TD]B12[/TD]
[TD]B14[/TD]
[TD]B36[/TD]
[TD]B45[/TD]
[TD]B68[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Smith[/TD]
[TD]B12, B36, B45[/TD]
[TD]X[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Jones[/TD]
[TD]B14, B36, B45[/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Paul[/TD]
[TD]B12, B14, B36, B45, B68[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Momentman]

In excel 2016, I might do something like
=TEXTJOIN(",",TRUE,IF($E4:$I4="X",$E$3:$I$3,"")) with CTRL+SHIFT+ENTER

 

[mattyblueice]

Hi Momentman - just trying to understand the ranges and how the formula would work, for example the formula would reside in the C2 and then copied down. I think you meant:

=TEXTJOIN(",",TRUE,IF($D2:$H2="X",$D$1:$H$1,""))

Does the first range D2:H2 search the row for an "X" and if True returns the value in the range D1:H1?

 

[Momentman]

Hi Momentman - just trying to understand the ranges and how the formula would work, for example the formula would reside in the C2 and then copied down. I think you meant:

=TEXTJOIN(",",TRUE,IF($D2:$H2="X",$D$1:$H$1,""))

Does the first range D2:H2 search the row for an "X" and if True returns the value in the range D1:H1?

Exactly - sorry about the range change

 

[mattyblueice]

Good Morning! Thanks - sorry for the long reply, I left work early. I put that in and did the Ctrl-Shift-enter and I get a #NAME ? error. Did I miss a step?

 

[Momentman]

Good Morning! Thanks - sorry for the long reply, I left work early. I put that in and did the Ctrl-Shift-enter and I get a #NAME ? error. Did I miss a step?

Are you using Excel 2016?

 

[mattyblueice]

Excel 2010 - my company is slow to adapt to new technology LOL

 

[Momentman]

That function wouldn't work over there, expectedly

 

[mattyblueice]

Just my luck HAHA - any options you can think of for Excel 2010?

 

[Mike Szczesny]

Can you use something like this. I first used [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$1:$F$1,SMALL(IF($A$2:$A$4=$H2,IF($B$2:$F$4="x",COLUMN($B$1:$F$1)-COLUMN($B$1)+1)),COLUMNS($J$2:J2)))," ") to find all the codes for the client. Then I used [TABLE="width: 95"]
<tbody>[TR]
[TD="width: 95"] =CONCATENATE(J2&","&" ",K2&"," &" ",L2) I just did one client but you should get the idea.

[TABLE="width: 863"]
<colgroup><col span="8"><col><col span="4"></colgroup><tbody>[TR]
[TD]Client[/TD]
[TD]B12[/TD]
[TD]B14[/TD]
[TD]B36[/TD]
[TD]B45[/TD]
[TD]B68[/TD]
[TD][/TD]
[TD]Client[/TD]
[TD]Concat Codes[/TD]
[TD]Codes[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Smith[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]Smith[/TD]
[TD]B12, B36, B45[/TD]
[TD]B12[/TD]
[TD]B36[/TD]
[TD]B45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jones[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]Jones[/TD]
[TD][/TD]
[TD]B14[/TD]
[TD]B36[/TD]
[TD]B45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD][/TD]
[TD]B12[/TD]
[TD]B14[/TD]
[TD]B36[/TD]
[TD]B45[/TD]
[/TR]
</tbody>[/TABLE]



[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]