Concatenating problems

[Guest]

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

 

[Guest]

Try this:

Dim i As Long
Dim LastRow As Long
Dim SaveResponse As String

LastRow = 7 '(whatever)

For i = LastRow To 3 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
SaveResponse = Cells(i, 2)
Cells(i, 1).EntireRow.Delete
Cells(i - 1, 2) = Cells(i - 1, 2) & " " & SaveResponse
End If
Next i

Column B should be formatted as Text

 

[Guest]

Thanks, but I don't think that's what I was shooting for. Either that or I'm
not properly utilizing the code (which is entirely possible).

 

[Guest]

Sorry, I missed the "into one column" part. I presume your data ranges from
"A2:B2" to "A<LastRow>:B<LastRow>". Also, I don't know if you want the
vertical bar "|" literally inserted into the result.

Dim i As Long
Dim LastRow As Long

LastRow = 7

For i = LastRow To 3 Step -1
If Cells(i, 1) = Cells(i - 1, 1) Then
Cells(i - 1, 2) = Cells(i - 1, 2) & " " & Cells(i, 2)
Cells(i, 1).EntireRow.Delete
LastRow = LastRow - 1
End If
Next i

For i = 2 To LastRow
Cells(i, 1) = Cells(i, 1) & " " & Cells(i, 2)
Cells(i, 2).ClearContents
Next i

 

[genas]

Hi,

I am interested if either of these solutions assisted you? I have the
same issue. I have tried both scripts but I am getting errors returned.
My VB skills are very rusty though. My columns are:

Room No | Guest Name
1 | Mr Smith
5 | Miss Green
5 | Mr Brown
6 | Mr & Mrs Gray

Regards,

Gena