Array formula & Concatenate

[janmomx3]

I am in need of summarizing responses to questions based on the respondent
marking their selection in column A with an X. I am assuming that an array
formula would do it for me. I want to concatenate the responses selected in
column A into column C (any row), not include any blanks (choices not
selected with the X) and separate each response with a comma. So it would
look like this: small group, tutorial, one on one training. Is an array
formula what I need? If so, how would I write it?

TIA for your help
janmomx3

A B C
1 large group
2 X small group
3 X tutorial
4 Modeling by a trainer
5 X one on one training
6 other

 

[Rick Rothstein \(MVP - VB\)]

Are you able to make use of a macro to do this?

Sub GetValuesNextToX()
Dim Cel As Range
Dim LastRow As Long
Dim Combo As String
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For Each Cel In ActiveSheet.Range("A1:A" & CStr(LastRow))
If Cel.Value = "X" Then
If Len(Combo) > 0 Then Combo = Combo & ", "
Combo = Combo & Cel.Offset(0, 1).Value
End If
Next
Range("C1").Value = Combo
End Sub

Rick

 

[Bernd P]

Hello,

Or array-entered:
=multicat(returnnonempty(IF(A1:A5="X",B1:B5,"")),",")

The UDF's are here:
http://www.sulprobil.com/html/concatenate.html

Regards,
Bernd