Array formula & Concatenate
 

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

Array formula & Concatenate
 

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


"janmomx3" wrote in message
...
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

Array formula & Concatenate
 

Hello,

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

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

Regards,
Bernd