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]

 

[Aladin Akyurek]

First, add the following code as a module to your workbook 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

Now in C2 control+shift+enter, not just enter:

=REPLACE(aconcat(IF($C2:$G2="X",", "&$C$1:$G$1,"")),1,2,"")

 

[Rick Rothstein]

Assuming the layout you showed in Message #1 (but assuming many more columns and rows of data), here is a UDF (user defined function), which takes no arguments, that you can use...

[table="width: 500"]
[tr]
	[td]Function Codes() As String
  Dim C As Long, LastCol As String, CallerCell As Range
  [B][COLOR="#FF0000"]Application.Volatile[/COLOR][/B]
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  Set CallerCell = Application.Caller
  For C = 3 To LastCol
    If Len(Cells(CallerCell.Row, C).Value) Then Codes = Codes & ", " & Cells(1, C).Value
  Next
  Codes = Mid(Codes, 3)
End Function[/td]
[/tr]
[/table]

NOTE: I made the function Volatile so that it will react if you add or remove any X's from your grid. If you won't need this functionality, you can remove the red highlighted line of code.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use Codes just like it was a built-in Excel function. For example, put this formula in cell B2 and copy it down...

=Codes()

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mattyblueice]

Hey Mike, I was putting your formula in, but what is the H2 supposed to be referencing and the J2 below?

Can you use something like this. I first used [TABLE="class: cms_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="class: cms_table, width: 95"]
<tbody>[TR]
[TD="width: 95"]=CONCATENATE(J2&","&" ",K2&"," &" ",L2) I just did one client but you should get the idea.

[TABLE="class: cms_table, width: 863"]
<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]

 

[Mike Szczesny]

H2 refers to Smith (at the right). The concatenated formula is in I2 (under Concat Codes). The =iferror(formula is in J2. Copy across.

 

[mattyblueice]

Thanks. Did you get a result in excel? I set this up exactly as you have if, copied it across and all I get al blank values.

 

[Mike Szczesny]

I was able to get the formulas to work as directed. Otherwise, I wouldn't have posted it. Did you just use enter when entering the formulas? Perhaps you should use Cntrl+Shift+Enter instead for these type of formulas. Try that.

 

[mattyblueice]

Ctrl+shift+enter was the last piece. It worked. Thanks for your help Mike. Have a good thanksgiving!