Thanks, Ken! There was a syntax error, and in
VB, the following was in red:
If Cells(Iloop, "A") & Cells(Iloop, "B") = Cells(Iloop - 1, "A") &
Cells(Iloop - 1, "B") Then
Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop,
"C")
I've never used
VB... But I assume it's red because that's where the error is?
Thanks so much!!
S
"Ken Hudson" wrote:
Sam,
I think that you will need code to do that.
Copy the code from below.
On your worksheet enter Alt+F11 to get to your VB Editor.
Go to Insert Module and paste the code.
Close the VB editor.
Go to Tools Macro Macros... and run the macro.
Make a backup copy of your workbook.
The macro will sort your data and it assumes that you have no header row. If
you have a header row, you'll need to change the sort code to "Header:=xlYes."
Sub Concat()
Dim Iloop As Integer
Dim Numrows As Integer
Dim Counter As Integer
Application.ScreenUpdating = False
Numrows = Range("A65536").End(xlUp).Row
Range("A1:C" & Numrows).Select
Selection.Sort key1:=Range("A1"), Order1:=xlAscending, _
Key2:=Range("B1"), Order2:=xlAscending, Key3:=Range("C1"), _
Order3:=xlAscending, Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
For Iloop = Numrows To 2 Step -1
If Cells(Iloop, "A") & Cells(Iloop, "B") = Cells(Iloop - 1, "A") &
Cells(Iloop - 1, "B") Then
Cells(Iloop - 1, "C") = Cells(Iloop - 1, "C") & ", " & Cells(Iloop,
"C")
Rows(Iloop).Delete
End If
Next Iloop
Range("A1").Select
Application.ScreenUpdating = True
End Sub
--
Ken Hudson
"savvysam" wrote:
I have a report that shows data like this:
A B C D E
1 Toy 8/3 Yellow
2 Bus 8/3 Blue
3 Bus 8/3 Green
4 Bus 8/4 Blue
I would like to consolidate rows that have the same data in colums A and B,
and to concatenate the data in column C in the new table. So it would look
like this.
A B C D E
1 Toy 8/3 Yellow
2 Bus 8/3 Blue, Green
3 Bus 8/4 Blue
Any suggestions on how I can do this? I was thinking if I could make 2 IF
statements, and have the second one concatenate if True... But not sure how
to do that.
Thanks!