Splitting Multiline Cells into Separate Rows

[kcjt2009]

Hello Everyone,

I am having a heck of a time trying to figure out how to solve this problem of mine. I found a couple of similar questions asked, but there weren't any applicable answers.

I basically have a five column spreadsheet containing address information. While most cells only contain one piece of information, I have quite a few cells that have multiple lines of data in them because the individual has more than one address.

Here is an example. Note that Rows 1-2 contain single address info for each individual person. However, row 3 contains an individual with 3 pieces of address information in the four right hand columns:

Row 1: Unique ID | Name | Street Address | City | State | Zip Code
Row 2: Unique ID | Name | Street Address | City | State | Zip Code
Row 3: Unique ID | Name | Street Address | City | State | Zip Code

[carriage return in street address, city, state and zip cells]

--------------------------------- |Street Address | City | State | Zip Code

[carriage return in street address, city, state and zip cells]

--------------------------------- |Street Address | City | State | Zip Code

Row 4: Unique ID | Name | Street Address | City | State | Zip Code
Row 5: Unique ID | Name | Street Address | City | State | Zip Code


Please note that some individuals have 2-4 different addresses.

I would like to manipulate the 5 rows of example data above so that when I am done I have the following:

1. 7 rows of data (5 individuals with 7 total addresses)
2. Automatically copy and paste the unique id number and name into the two new rows that were created so that I have the following:

Row 1: Unique ID - 001 | Joe Chang | Street Address | City | State | Zip Code
Row 2: Unique ID - 002 | Joey Chung | Street Address | City | State | Zip Code
Row 3: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 4: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 5: Unique ID - 003 | John Smith | Street Address | City | State | Zip Code
Row 6: Unique ID - 004 | Jane Derry | Street Address | City | State | Zip Code
Row 7: Unique ID - 005 | Julie March | Street Address | City | State | Zip Code

Your help is kindly appreciated. Thank you.

 

[hiker95]

kcjt2009,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


We would like to see some actual raw data.

So that we can get it right the first time:

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[kcjt2009]

I am using the 2013 Office Suite and Windows 8.

Below you will see a small sample with the header row and five additional rows of data. I am trying to change rows that look like the yellow one that I have highlighted. They can have anywhere from 2-4 addresses in a single row.

I am trying to extract the information to create rows below it while pushing the other text down without deleting it.



Excel 2012

	A	B	C	D	E	F
1	ID	Name	Street	City	Zip	State
2	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
3	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
4	6666	DAVIS,JONATHAN	3725 MORTENSEN RD 2400 LECONTE AVE	SAN LEANDRO BERKELEY	94578 94705	CA CA
5	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
6	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
7
8
9
10	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
11	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
12	6666	DAVIS,JONATHAN	3725 MORTENSEN RD	SAN LEANDRO	94578	CA
13	6666	DAVIS,JONATHAN	2400 LECONTE AVE	BERKELEY	94705	CA
14	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
15	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula A1 ID B1 Name C1 Street D1 City E1 Zip F1 State A2 4444 B2 FLOWERS,LEONARD C2 141 89TH AVE APT # D D2 OAKLAND E2 94621 F2 CA A3 5555 B3 FLUKER,FREDRICK LEWIS C3 721 HILLSIDE ST D3 OAKLAND E3 94605 F3 CA A4 6666 B4 DAVIS,JONATHAN C4 3725 MORTENSEN RD 2400 LECONTE AVE D4 SAN LEANDRO BERKELEY E4 94578 94705 F4 CA CA A5 7777 B5 FRANCO,BALTAZAR C5 17009 MONTROSE AVE D5 SAN LORENZO E5 94580 F5 CA A6 8888 B6 FRANKLIN,CURTIS EUGENE C6 6234 15TH ST APT # 610 D6 OAKLAND E6 94612 F6 CA A7 B7 C7 D7 E7 F7 A8 B8 C8 D8 E8 F8 A9 B9 C9 D9 E9 F9 A10 4444 B10 FLOWERS,LEONARD C10 141 89TH AVE APT # D D10 OAKLAND E10 94621 F10 CA A11 5555 B11 FLUKER,FREDRICK LEWIS C11 721 HILLSIDE ST D11 OAKLAND E11 94605 F11 CA A12 6666 B12 DAVIS,JONATHAN C12 3725 MORTENSEN RD D12 SAN LEANDRO E12 94578 F12 CA A13 6666 B13 DAVIS,JONATHAN C13 2400 LECONTE AVE D13 BERKELEY E13 94705 F13 CA A14 7777 B14 FRANCO,BALTAZAR C14 17009 MONTROSE AVE D14 SAN LORENZO E14 94580 F14 CA A15 8888 B15 FRANKLIN,CURTIS EUGENE C15 6234 15TH ST APT # 610 D15 OAKLAND E15 94612 F15 CA <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[hiker95]

kcjt2009,

Sample raw data:

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Street	City	Zip	State
2	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
3	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
4	6666	DAVIS,JONATHAN	3725 MORTENSEN RD 2400 LECONTE AVE	SAN LEANDRO BERKELEY	94578 94705	CA CA
5	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
6	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
7
8
Sheet3

After the macro:

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Street	City	Zip	State
2	4444	FLOWERS,LEONARD	141 89TH AVE APT # D	OAKLAND	94621	CA
3	5555	FLUKER,FREDRICK LEWIS	721 HILLSIDE ST	OAKLAND	94605	CA
4	6666	DAVIS,JONATHAN	3725 MORTENSEN RD	SAN LEANDRO	94578	CA
5	6666	DAVIS,JONATHAN	2400 LECONTE AVE	BERKELEY	94705	CA
6	7777	FRANCO,BALTAZAR	17009 MONTROSE AVE	SAN LORENZO	94580	CA
7	8888	FRANKLIN,CURTIS EUGENE	6234 15TH ST APT # 610	OAKLAND	94612	CA
8
Sheet3

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 02/14/2014, ME757628
Dim r As Long, lr As Long, s, i As Long
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 3).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 3), vbLf) Then
    s = Split(Cells(r, 3), vbLf)
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r + 1, 1).Resize(UBound(s), 2).Value = Cells(r, 1).Resize(, 2).Value
    Cells(r, 3).Resize(UBound(s) + 1).Value = Application.Transpose(s)
    s = Split(Cells(r, 4), vbLf)
    Cells(r, 4).Resize(UBound(s) + 1).Value = Application.Transpose(s)
     s = Split(Cells(r, 5), vbLf)
    Cells(r, 5).Resize(UBound(s) + 1).Value = Application.Transpose(s)
    s = Split(Cells(r, 6), vbLf)
    Cells(r, 6).Resize(UBound(s) + 1).Value = Application.Transpose(s)
  End If
Next r
Range("A2:A" & lr).Rows.AutoFit
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.

 

[kcjt2009]

Hiker

Thank you so much for taking the time to help me out with this. On the practice data, it worked great. It actually created a new row for each line of data including the carriage return in the cells, which I kind of prefer.

Now, when I take it to my actual data set, I am getting a Type 13 error and cannot figure out why. When I open the debugger it points to this line and highlights it in yellow;

Cells(r, 4).Resize(UBound(s) + 1).Value = Application.Transpose(s)


After I look at the highlighted line and close the debugger, I notice that the macro changed some of the rows of data the way it is supposed to...but it only changed about 200 rows of data out of around 3400 records. And interestingly enough, nearly all the records it changed were towards the end of the sheet (last names beginning with "w". Although there were a couple of strays and it did not change all the names starting with a "w".

I have tried a smaller data set from the same list and sometimes it works just fine without issue and sometimes it presents the Type 13 error but still manages to complete some of the row splits.

Not sure how to go about providing you with the info to help me fix this.

 

[hiker95]

kcjt2009,

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.

If your actual raw is very different from your original screenshot(s), then, I would prefer the following:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[kcjt2009]

My apologies. Here is the link:

https://app.box.com/s/t1uisiembiryq6o8taby

I originally deleted the "Show On Map" text in the address cells, but left it in since the code you provided above basically put it on its own row and i could simply delete those later.

I am thinking that perhaps some rows have more than 4 lines of data in each cell, but I don't know how to read your code and wasn't sure how to tweak it to make it account for say a cell with 12 lines of text. And I am not even sure that that is the issue.

Thank you!

 

[hiker95]

kcjt2009,

Can I have another workbook, with the raw data in worksheet Sheet1, and, in worksheet Results, what the results should be for raw data rows 2, 17, 32, 45, 90 (raw data for these rows are shown below):

Excel 2007
	A	B	C	D	E	F
1	ID	Name	Address	City	ZipCode	County
2	javascript: OpenDetail('18611314B2241') Row 2	AARON,JEFFREY DALE	25494 JASPER RD Show On Map	BARSTOW		SAN BERNARDINO
17	javascript: OpenDetail('38210041N4620') Row 17	ADELMAN,GARY JOEL	PO BOX 464 Show On Map 553 W VICTORIA CT Show On Map	SKYFOREST LAKE ARROWHEAD	92385 92352	SAN BERNARDINO SAN BERNARDINO
32	javascript: OpenDetail('18600227E3520') Row 32	AGUIRRE,RAFAEL	15814 MIRASOL CT POB 168 15814 MIRASOL CT Show On Map	HELENDALE HELENDALE	92342 92342	SAN BERNARDINO SAN BERNARDINO
46	javascript: OpenDetail('18602239G7839') Row 46	ALDANA,VICTOR DAVID	7149 N BARTON STREET Show On MapSpecific address not subject to disclosure	SAN BERNARDINO	92404	SAN BERNARDINO
90	javascript: OpenDetail('43611333B2584') Row 90	ANDERSON,JERRY JR	7054 JUNIPER AVE Show On Map 8783 SAN VINCENTE DR Show On Map	TWENTYNINE PALMS YUCCA VALLEY	92277 92284	SAN BERNARDINO SAN BERNARDINO
Sheet1

 

[kcjt2009]

Ideally it would look like the following:

https://app.box.com/s/t1uisiembiryq6o8taby

I am eventually going to delete all the additional rows that say "Show On Map".

 

[hiker95]

kcjt2009,

The structure of your actual raw data is nothing like what your original screenshot depicted.

I have not been able to come up with a macro that will give you the results you are looking for.

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.