0

I have a spreadsheet being used to manage content while a site is being developed. There are columns for keyword tags (subcategory IDs), and an asset row is checked with an X if that tag applies to that asset. This extends from column Z through column GT.

I would like to find a way to list in one cell all of the tags applied to an asset.

I found a terrific post from this site last week about pulling in a column heading for the first value in a row, which worked for a task I was doing last week--now I'd like to find out how to pull in the column heading for every value in this range.

This is the formula I copied for the previous effort:

=IF(COUNTA($Z2:$GT2)=0,"",INDEX($Z$1:$GT$1,MATCH(TRUE,INDEX($Z2:$GT2<>"",0),0)))

Thanks for any tips!

wondergirl
  • 1
  • 2
  • This sounds like something that would be easy to do with a macro, is that something you're open to? – TMH8885 May 28 '15 at 19:44
  • Does this link solve your problem? http://stackoverflow.com/questions/22639868/vba-user-defined-function-for-concatenate-if-by-rows – jason a May 28 '15 at 19:45
  • Definitely open to using a macro--I'm reading the comments on the suggested link now. Thank you both! – wondergirl May 28 '15 at 19:53

1 Answers1

0

Building a rather easy formula: 

Function ConcXs(rngRow As Range)
Dim strResult As String
strResult = ""
For Each rngFor In rngRow
    If rngFor.Value = "X" Then strResult = strResult + (Cells(1, rngFor.Column).Value)
Next
ConcXs = strResult
End Function

You can then use this formula in excel, put in the range (Z3:GT3 for example) and it will find any Xs in that range, and concatenate the headers (assumed on row 1) for each. You can modify if you need commas, spaces, etc in your result.

TMH8885
  • 888
  • 6
  • 15