Comma Separated Answers After a Lookup

[fatgagne76]

I have a table like the following (ignore the periods):

address phone email fax
1 x ................ x
2 x ....... x ...... x ....x
3 x ....... x
4 x

I want to create a column to the right of fax.

In that column, I want the heading names of each cell that contains an X.

For example, for Row 1, the new column would say "address, email"

Any suggestions?

 

[T. Valko]

I have a table like the following (ignore the periods):

address phone email fax
1 x ................ x
2 x ....... x ...... x ....x
3 x ....... x
4 x

I want to create a column to the right of fax.

In that column, I want the heading names of each cell that contains an X.

For example, for Row 1, the new column would say "address, email"

Any suggestions?

Here's a brute force method assuming that's all the columns of data you actually have...

Book1
	B	C	D	E	F
1	address	phone	email	fax	Combined
2	x	_	x	_	address, email
3	x	x	x	x	address, phone, email, fax
4	x	x	_	_	address, phone
5	x	_	_	_	address
6	_	_	_	_
Sheet1

Formula entered in F2 and copied down:

=IF(B2="x",B$1&IF(COUNTIF(C2:E2,"x"),", ",""),"")&
IF(C2="x",C$1&IF(COUNTIF(D2:E2,"x"),", ",""),"")&
IF(D2="x",D$1&IF(E2="x",", ",""),"")&IF(E2="x",E$1,"")

 

[fatgagne76]

Thank you for your help. Unfortunately, I actually have about 35 columns instead of 3.

Any other thoughts?

 

[T. Valko]

Thank you for your help. Unfortunately, I actually have about 35 columns instead of 3.

Any other thoughts?

You'll need a VBA user defined function to do this.

You could search the site for a "concatenate if" UDF. I'm sure there's a lot of examples.

Or, someone may see this thread and provide the code for you.

 

[Roli001]

Try this:

Sub ConcatenateColumnHeading()

endcolumn = [a1].End(xlToRight).Column
endRow = ActiveSheet.UsedRange.Rows.Count
    
For i = 2 To endRow
    For j = 1 To endcolumn
        If Not IsEmpty(Cells(i, j)) Then
            If j = 1 Then
                Cells(i, endcolumn + 1) = Cells(1, j)
            Else
                Cells(i, endcolumn + 1) = Cells(i, endcolumn + 1) & "," & Cells(1, j)
            End If
        End If
    Next j
Next i

End Sub

 

[fatgagne76]

Thank you very much. I have not used UDFs before, so it will take me a couple of tries to get it down. I will post tomorrow whether or not it works.

Thanks again

 

[Roli001]

This is Visual Basic Code and needs to be run from the VBc editor. You can access the VB editor by pressing ALT + F11 from Excel.

Good luck.

 

[Aladin Akyurek]

Thank you for your help. Unfortunately, I actually have about 35 columns instead of 3.

Any other thoughts?

Having the results in a single cell in D requires that you add
the following function code in VBA to your workbook as a module.

In order to add the function to your workbook, run...

ALt+F11
Insert | Module
File | Close and Return to Microsoft Excel.
_________________________________________________________

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 equipped with ACONCAT...

Assuming that the sample you posted is in A1:D4, the headers included...

E2, control+shift+enter, not just enter, and copy down:

=SUBSTITUTE(aconcat(IF(A2:D2="x",", "&$A$1:$D$1,"")),", ","",1)

 

[mikerickson]

My UDF is a bit more complicated.
The first three arguements of ConcatIf mirror those of SUMIF, the Delimiter and NoDuplicates are optional and, hopefuly, self explainitory.

In your case, put =ConcatIf(B2:E2, "X", $B$1:$E$1, ",") in F2

Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

 

[fatgagne76]

You guys are amazing. Thanks for all of the help.