Pivot table for worksheet text fields?

[Tom Mitrano]

Can a pivot table concatenate text that appears in a worksheet?

I often use Excel to lay out questionnaire responses. Pivot tables are
great to calculate and create cross-tabulations of data: Of those who
answered Yes to Question 3, how many also answered No to Question 4?

But I usually include open ended prompts in my questionnaire: Questions 4:
If your answer to Question 3 is Yes, please explain why.

Is there a way to use a Pivot table to concatenate (compile into one cell)
all the text answers given in response to Question 4?

If not, is there some other Excel function (Array?) that I can use to
perform the same task?

Thank you,
Tom

 

[Eva]

Hi Tom
I am not sure what you want to accomplish but there is the formula to
compile all cells into one : CONCATENATE
Pivot table doesn't do things like that, but you can put your Questions 4
column into ROW section on your pivot table and hide all totals.

 

[ryguy7272]

Good question! You probably want the first function, unless you want to
represent blanks with commas:

Function ConCatRange2(CellBlock As Range) As String
'for non-contiguous cells =ccr((a1:a10,c4,c6,e1:e5))
Dim Cell As Range
Dim sbuf As String
For Each Cell In CellBlock
If Len(Cell.Text) > 0 Then sbuf = sbuf & Cell.Text & ","
Next
ConCatRange2 = Left(sbuf, Len(sbuf) - 1)
End Function

Function mergem(r As Range) As String
mergem = r.Cells(1, 1).Value
k = 1
For Each rr In r
If k <> 1 Then
mergem = mergem & "," & rr.Value
End If
k = 2
Next
End Function

Function spliceUm(r As Range) As String
spliceUm = ""
For Each rr In r
spliceUm = spliceUm & rr.Value & ";"
Next
End Function