Consolidate text with same unique identifier

sarcastress

New Member
Joined
Jul 9, 2008
Messages
13
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
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
See if this works for you...Assumes ID is column A and Text is Column B and output goes to Column C

Code:
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
ABC
1IDText
2345This is a sentence thatThis 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!
3345has line breaks in it, but
4345needs to be consolidated
5345into one cell, if you would
6345be so kind as to help me.
7345KTHX!
8346Oh no, here's another oneOh no, here's another one, that has a different number, of rows. Eep!
9346that has a different number
10346of rows. Eep!
Sheet1
Excel 2007
 
Last edited:

sarcastress

New Member
Joined
Jul 9, 2008
Messages
13
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

Well-known Member
Joined
Oct 6, 2004
Messages
3,482

ADVERTISEMENT

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

New Member
Joined
Jul 9, 2008
Messages
13
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

Well-known Member
Joined
Oct 6, 2004
Messages
3,482

ADVERTISEMENT

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

New Member
Joined
Jul 9, 2008
Messages
13
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

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
when you are in debug...hover your mouse over dataRow and myData
what values does it show?
 

sarcastress

New Member
Joined
Jul 9, 2008
Messages
13
Oh, I'm an idiot. I didn't have column headings in my spreadsheet. Once I put them in, it worked great.

Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,474
Messages
5,601,872
Members
414,479
Latest member
Beau the dog

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top