IF, IF, Concatenate?

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

The first two lines are actually one continuous line. The 3rd and 4th are
also one line.
Put your cursor at the end of the first line and hit the delete key until
the second line jumps up to the end of the first line. Do the same for the
3rd and 4th line.
Post back if this isn't clear.

 

[Guest]

YYYAAAAY! Thanks so much, it worked!! You've just saved me LOTS of time.
Thanks again!

Samantha

 

[Guest]

You're welcome.
In looking over the code I see that I actually coded it for the case where
you have a header row.
If you don't have a header row, to be safe, you should open the VB editor
(Alt-F11), open Module1, and change the part of the code from "Header:=xlYes"
to "Header:=xlNo."
Then close the editor and save the workbook.

 

[Guest]

No worries, I caught that. Thanks!

Samantha

You're welcome.
In looking over the code I see that I actually coded it for the case where
you have a header row.
If you don't have a header row, to be safe, you should open the VB editor
(Alt-F11), open Module1, and change the part of the code from "Header:=xlYes"
to "Header:=xlNo."
Then close the editor and save the workbook.