Hello,

I have an Excel sheet with a header row containing the following fields:

Customer ID | Numeric Response

The rows beneath contain data as such:

5 | 1
5 | 4
5 | 7
7 | 2
7 | 3
8 | 1

Basically Customer ID "5" answered 1,4, and 7 and has a separate row for
each answer (same with Customer ID "7" answering 2 and 3, etc...)

I would like to concatenate the Response row into one field so that
ultimately I have one column that looks like:

Customer ID | Numeric Response

5 | 1 4 7
7 | 2 3
8 | 1
etc....

Thank you in advance,
Jack

You may use a macro like this:
Sub ConcatenateID()
Dim rngOrig, rngDest As Range
Dim i, d, a, Values

Set d = CreateObject("Scripting.Dictionary")
'This is the starting range of the current data
Set rngOrig = Worksheets("Sheet3").Range("A1")
'This is the starting range of the destination
Set rngDest = Worksheets("Sheet3").Range("C1")
i = 0

'Gather the information, loop until we find a blank
Do While rngOrig.Offset(i, 0).Value < ""
Values = Split(rngOrig.Offset(i, 0).Value, " | ")
If d.exists(Values(0)) Then
d(Values(0)) = d(Values(0)) & " " & Values(1)
Else
d.Add Values(0), Values(1)
End If
i = i + 1
Loop

'Print the information in the destination range
a = d.keys
For i = 0 To d.Count - 1
rngDest.Offset(i, 0).Value = a(i) & " | " & d(a(i))
Next
End Sub

Hope this helps,
Miguel.

"Jack" wrote:

Hello,

I have an Excel sheet with a header row containing the following fields:

Customer ID | Numeric Response

The rows beneath contain data as such:

5 | 1
5 | 4
5 | 7
7 | 2
7 | 3
8 | 1

Basically Customer ID "5" answered 1,4, and 7 and has a separate row for
each answer (same with Customer ID "7" answering 2 and 3, etc...)

I would like to concatenate the Response row into one field so that
ultimately I have one column that looks like:

Customer ID | Numeric Response

5 | 1 4 7
7 | 2 3
8 | 1
etc....

Thank you in advance,
Jack