Consolidate text with same unique identifier

[sarcastress]

Hello there. I think a macro could probably do this, but I am worthless with VBA. What I need to do is consolidate lines of text if they have the same numeric identifier. See below.

ID Text
345 This is a sentence that
345 has line breaks in it, but
345 needs to be consolidated
345 into one cell, if you would
345 be so kind as to help me.
345 KTHX!
346 Oh no, here's another one
346 that has a different number
346 of rows. Eep!

Not sure how to consolidate text or concatenate it, since it's not always going to be the same number of rows each time this happens. I need something with an if statement and...some other crap. Can someone help?

Thanks!
April

 

[TheNoocH]

See if this works for you...Assumes ID is column A and Text is Column B and output goes to Column C

Sub test2()
    myData = ""
    For Each cell In Range(Cells(2, "B"), Cells(Cells(Rows.Count, "B").End(xlUp).Row, "B"))
       If cell.Offset(0, -1)<> cell.Offset(-1, -1) Then
          If cell.Row<> 2 Then Cells(dataRow, 3).Value = myData
          myData = cell.Value
          dataRow = cell.Row
       Else
          myData = myData & ", " & cell.Value
       End If
    Next cell
    Cells(dataRow, 3).Value = myData
End Sub

This is the result after running the macro...

Excel Workbook
	A	B	C
1	ID	Text
2	345	This is a sentence that	This is a sentence that, has line breaks in it, but, needs to be consolidated, into one cell, if you would, be so kind as to help me., KTHX!
3	345	has line breaks in it, but
4	345	needs to be consolidated
5	345	into one cell, if you would
6	345	be so kind as to help me.
7	345	KTHX!
8	346	Oh no, here's another one	Oh no, here's another one, that has a different number, of rows. Eep!
9	346	that has a different number
10	346	of rows. Eep!
Sheet1

Excel 2007

 

[dave3009]

Or the ConcatIf UDF as featured >>here<<

 

[sarcastress]

I am completely inept with visual basic. What info do I need to replace in what you've written with my own data (column headings, etc)? Sorry for the preschool level understanding here.

 

[TheNoocH]

To install code --> ALT-F11, Insert, Module...Paste the code from Sub <---> End Sub into the blank area

Then either TOOLS, MACRO, MACROS, select the macro and RUN
or in Office 2007
Developer Tab, MACROS, select the macro and RUN

 

[sarcastress]

Oh, I got that far, but when I ran it I got an error message that just said "400" so I assumed I needed to customize it first.

 

[TheNoocH]

where did it say 400? did it say anything besides that?
did you put the code in with ALT-F11 or did you right click on the worksheet and hit view code?

 

[sarcastress]

Okay, I figured out what I did there, but now I have a run-time error "1004" and when I go into debug it's highlighting this line:

Cells(dataRow, 3).Value = myData

I feel like I may be the dumbest person in here - sorry!

 

[TheNoocH]

when you are in debug...hover your mouse over dataRow and myData
what values does it show?

 

[sarcastress]

Oh, I'm an idiot. I didn't have column headings in my spreadsheet. Once I put them in, it worked great.

Thank you!