|
IF, IF, Concatenate?
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! |
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! |
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! |
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. -- Ken Hudson "savvysam" wrote: 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! |
YYYAAAAY! Thanks so much, it worked!! You've just saved me LOTS of time.
Thanks again! Samantha "Ken Hudson" wrote: 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. -- Ken Hudson "savvysam" wrote: 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! |
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. -- Ken Hudson "savvysam" wrote: YYYAAAAY! Thanks so much, it worked!! You've just saved me LOTS of time. Thanks again! Samantha "Ken Hudson" wrote: 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. -- Ken Hudson "savvysam" wrote: 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! |
No worries, I caught that. Thanks!
Samantha "Ken Hudson" wrote: 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. -- Ken Hudson "savvysam" wrote: YYYAAAAY! Thanks so much, it worked!! You've just saved me LOTS of time. Thanks again! Samantha "Ken Hudson" wrote: 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. -- Ken Hudson "savvysam" wrote: 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! |
| All times are GMT +1. The time now is 01:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com