Excel Formula: List headers where cell has value

2
votes

What is the formula to show the value in the header if the cell has data for all cells in a row? The table has many columns.

Example Data:

+----+----+-------+
|One |Two |Output |
+----+----+-------+
|data|    |One    |
+----+----+-------+
|    |data|Two    |
+----+----+-------+
|data|data|One,Two|
+----+----+-------+
|    |    |       |
+----+----+-------+
1
excelexcel-formula
Does your version of Excel support the TEXTJOIN function? (requires Excel 2016). If not, this will require VBA. – tigeravatar
I am using Excel for Mac 2011 – jbgroce21
In that case you will need to create a UDF (User Defined Function) within VBA (Visual Basic for Applications). Something like this should get you started: stackoverflow.com/questions/22639868/… – tigeravatar
How many columns do you have ? While VBA is the best way to go, I can see a dirty IF function that could do the trick if you don't have a lot of columns. – Lich4r
It's over 100. I'm looking into a VBA script now – jbgroce21

1 Answers

0
votes

Because of the constraints of Excel for Mac 2011, I could not find a formula that worked so I used a User Defined Function.

Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, _
        ConcatenateRange As Range, Optional Separator As String = ",") As Variant
    Dim i As Long
    Dim strResult As String
    On Error GoTo ErrHandler
    If CriteriaRange.Count <> ConcatenateRange.Count Then
        ConcatenateIf = CVErr(xlErrRef)
        Exit Function
    End If
    For i = 1 To CriteriaRange.Count
        If CriteriaRange.Cells(i).Value = Condition Then
            strResult = strResult & Separator & ConcatenateRange.Cells(i).Value
        End If
    Next i
    If strResult <> "" Then
        strResult = Mid(strResult, Len(Separator) + 1)
    End If
    ConcatenateIf = strResult
    Exit Function
ErrHandler:
    ConcatenateIf = CVErr(xlErrValue)
End Function

Then in the cell used 

=ConcatenateIf($A1:$B1, "data", $A$1:$B$1, ",")

Credit to this link