Thread: Concatenating problems

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

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

"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

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).

"Charlie" wrote:

> 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
>
> "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

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


"Jack" wrote:

> 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).
>
> "Charlie" wrote:
>
> > 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
> >
> > "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

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