LinkBack URL
About LinkBacks
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.
--
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!
> > > > >
Register To Reply
Bookmarks