Excel Formula: List headers where cell has value

Question

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|
+----+----+-------+
|    |    |       |
+----+----+-------+

Does your version of Excel support the [TEXTJOIN](https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c) function? (requires Excel 2016). If not, this will require VBA. — tigeravatar, May 08 '17 at 18:47
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: http://stackoverflow.com/questions/22639868/vba-user-defined-function-for-concatenate-if-by-rows — tigeravatar, May 08 '17 at 18:56
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, May 08 '17 at 19:27
@tigeravatar your link got me pretty much there. Do you want to put it in an answer or should I just delete the question? — jbgroce21, May 08 '17 at 21:15
@jbgroce21 I'm glad that it helped! Since I did not provide the code, and a simple link does not meet the requirements for an answer on this site, there is no need for me to post that as an answer. I would recommend that you post the code you created which resolved this issue as an answer for future viewers. — tigeravatar, May 09 '17 at 13:03

score 0 · Accepted Answer · edited May 23 '17 at 10:31

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

Excel Formula: List headers where cell has value

1 Answers1