Need help parsing multiple entries from a single cell

[BlackCat]

Greetings,

I'm exporting data from a Rational database application into an Excel 2010 spreadsheet. I then create a pivot table to display the data as shown in the Data Source example (row 4) shown below. In cells D4 thru F4, I have a single row with multiple entries that are separately by what appears to be the ALT+ENTER character.

The real output I need shown in the Desired Output example (rows 8 thru 13) below. I'd like to have a macro that will parse all of the combined cells into rows with single values. I would also like to have the macro ignore the first header row of the spreadsheet, then perform its actions on how ever many rows are in the spreadsheet, stopping when it reached the end of populated rows.

Can someone please provide me with a simple macro to do this?

Thanks in advance.

Paul


Excel 2010

	A	B	C	D	E	F	G
2	Data Source
3
4	Luggage	Large	Brown	ABC-XYZ-29 ABC-XYZ-494 ABC-XYZ-1700 ABC-XYZ-1701 ABC-XYZ-1703 ABC-XYZ-1704	Vinyl Suede Leather Leather Leather Leather	Brass Gold Silver Silver Silver Silver	Lorem Ipsum is simply dummy text.
5
6	Desired Output
7
8	Luggage	Large	Brown	ABC-XYZ-29	Vinyl	Brass	Lorem Ipsum is simply dummy text.
9	Luggage	Large	Brown	ABC-XYZ-494	Suede	Gold	Lorem Ipsum is simply dummy text.
10	Luggage	Large	Brown	ABC-XYZ-1700	Leather	Silver	Lorem Ipsum is simply dummy text.
11	Luggage	Large	Brown	ABC-XYZ-1701	Leather	Silver	Lorem Ipsum is simply dummy text.
12	Luggage	Large	Brown	ABC-XYZ-1703	Leather	Silver	Lorem Ipsum is simply dummy text.
13	Luggage	Large	Brown	ABC-XYZ-1704	Leather	Silver	Lorem Ipsum is simply dummy text.

<TBODY>
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #bfbfbf"]Class
[/TD]
[TD="bgcolor: #bfbfbf"]Size
[/TD]
[TD="bgcolor: #bfbfbf"]Color
[/TD]
[TD="bgcolor: #bfbfbf"]Part Number
[/TD]
[TD="bgcolor: #bfbfbf"]Material
[/TD]
[TD="bgcolor: #bfbfbf"]Hardware
[/TD]
[TD="bgcolor: #bfbfbf"]Text
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #bfbfbf"]Class
[/TD]
[TD="bgcolor: #bfbfbf"]Size
[/TD]
[TD="bgcolor: #bfbfbf"]Color
[/TD]
[TD="bgcolor: #bfbfbf"]Part Number
[/TD]
[TD="bgcolor: #bfbfbf"]Material
[/TD]
[TD="bgcolor: #bfbfbf"]Hardware
[/TD]
[TD="bgcolor: #bfbfbf"]Text
[/TD]

</TBODY>

Sample​

 

[hiker95]

BlackCat,

Welcome to the MrExcel forum.


I assume that the group of cells in columns D, E, and F, same row, contain the same number of entries.


Sample raw data:

Excel Workbook
	A	B	C	D	E	F	G
1
2	Data Source
3	Class	Size	Color	Part Number	Material	Hardware	Text
4	Luggage	Large	Brown	ABC-XYZ-29ABC-XYZ-494ABC-XYZ-1700ABC-XYZ-1701ABC-XYZ-1703ABC-XYZ-1704	VinylSuedeLeatherLeatherLeatherLeather	BrassGoldSilverSilverSilverSilver	Lorem Ipsum is simply dummy text
5	Luggage#2	Large#2	Brown#2	ABC-XYZ-29ABC-XYZ-494ABC-XYZ-1700ABC-XYZ-1701ABC-XYZ-1703ABC-XYZ-1704	VinylSuedeLeatherLeatherLeatherLeather	BrassGoldSilverSilverSilverSilver	Lorem Ipsum is simply dummy text#2
6	Luggage#3	Large#3	Brown#3	ABC-XYZ-29ABC-XYZ-494ABC-XYZ-1700ABC-XYZ-1701ABC-XYZ-1703ABC-XYZ-1704	VinylSuedeLeatherLeatherLeatherLeather	BrassGoldSilverSilverSilverSilver	Lorem Ipsum is simply dummy text#3
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

After the macro:

Excel Workbook
	A	B	C	D	E	F	G
1
2	Data Source
3	Class	Size	Color	Part Number	Material	Hardware	Text
4	Luggage	Large	Brown	ABC-XYZ-29	Vinyl	Brass	Lorem Ipsum is simply dummy text
5	Luggage	Large	Brown	ABC-XYZ-494	Suede	Gold	Lorem Ipsum is simply dummy text
6	Luggage	Large	Brown	ABC-XYZ-1700	Leather	Silver	Lorem Ipsum is simply dummy text
7	Luggage	Large	Brown	ABC-XYZ-1701	Leather	Silver	Lorem Ipsum is simply dummy text
8	Luggage	Large	Brown	ABC-XYZ-1703	Leather	Silver	Lorem Ipsum is simply dummy text
9	Luggage	Large	Brown	ABC-XYZ-1704	Leather	Silver	Lorem Ipsum is simply dummy text
10	Luggage#2	Large#2	Brown#2	ABC-XYZ-29	Vinyl	Brass	Lorem Ipsum is simply dummy text#2
11	Luggage#2	Large#2	Brown#2	ABC-XYZ-494	Suede	Gold	Lorem Ipsum is simply dummy text#2
12	Luggage#2	Large#2	Brown#2	ABC-XYZ-1700	Leather	Silver	Lorem Ipsum is simply dummy text#2
13	Luggage#2	Large#2	Brown#2	ABC-XYZ-1701	Leather	Silver	Lorem Ipsum is simply dummy text#2
14	Luggage#2	Large#2	Brown#2	ABC-XYZ-1703	Leather	Silver	Lorem Ipsum is simply dummy text#2
15	Luggage#2	Large#2	Brown#2	ABC-XYZ-1704	Leather	Silver	Lorem Ipsum is simply dummy text#2
16	Luggage#3	Large#3	Brown#3	ABC-XYZ-29	Vinyl	Brass	Lorem Ipsum is simply dummy text#3
17	Luggage#3	Large#3	Brown#3	ABC-XYZ-494	Suede	Gold	Lorem Ipsum is simply dummy text#3
18	Luggage#3	Large#3	Brown#3	ABC-XYZ-1700	Leather	Silver	Lorem Ipsum is simply dummy text#3
19	Luggage#3	Large#3	Brown#3	ABC-XYZ-1701	Leather	Silver	Lorem Ipsum is simply dummy text#3
20	Luggage#3	Large#3	Brown#3	ABC-XYZ-1703	Leather	Silver	Lorem Ipsum is simply dummy text#3
21	Luggage#3	Large#3	Brown#3	ABC-XYZ-1704	Leather	Silver	Lorem Ipsum is simply dummy text#3
22
Sheet1

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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/11/1213
' http://www.mrexcel.com/forum/excel-questions/678872-need-help-parsing-multiple-entries-single-cell.html
Dim r As Long, lr As Long, d As Long, e As Long, f As Long
Dim s1, s2, s3
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 4 Step -1
  s1 = Split(Cells(r, 4), Chr(10))
  s2 = Split(Cells(r, 5), Chr(10))
  s3 = Split(Cells(r, 6), Chr(10))
  Rows(r + 1).Resize(UBound(s1)).Insert
  Cells(r + 1, 1).Resize(UBound(s1), 3).Value = Cells(r, 1).Resize(, 3).Value
  Cells(r + 1, 7).Resize(UBound(s1)).Value = Cells(r, 7).Value
  Cells(r, 4).Resize(UBound(s1) + 1).Value = Application.Transpose(s1)
  Cells(r, 5).Resize(UBound(s2) + 1).Value = Application.Transpose(s2)
  Cells(r, 6).Resize(UBound(s3) + 1).Value = Application.Transpose(s3)
Next r
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.

 

[BlackCat]

hiker95,

Sorry for the late response. I did see your quick response and appreciate the work you've done. I don't often have free moments at work to apply macros for reports.

Your macro works fine as posted for exactly the Sample raw data you posted (rows 4 thru 6, luggage thru luggage#3). Can you please help me fine tune the macro? In my real report extracts, I will always have a table header in row 1 (Class, Size, Color, etc.). However, the real report output may range from a few dozen rows to hundreds of rows. I would like a macro that starts the loop on row 2, and then continues working down the sheet until it reaches the last populated row. The output can even be written to a new sheet in the workbook while the macro is working through the rows in an existing sheet. Does this make sense?


Row 1 (Header)
Row 2 (Data)
Row 3 (Data)
Row 4 (Data)
...
Row 200 (Data)

Can you help me fine tune this macro so that it can work on a couple of hundred rows at once?

Thanks!

Paul

 

[hiker95]

BlackCat,

Can you please help me fine tune the macro? In my real report extracts

I will have to see the real raw data.

Can you post the raw data worksheet, and, post 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.

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

 

[BlackCat]

hiker95,

Thanks again for your help. Let me create a raw data worksheet with results and I will post it to Box Net.

Paul

 

[BlackCat]

hiker95,

I have generated a sample report file and posted it to box.net. I have used CTRL+H to replace all of the actual values with changed values. Otherwise, the structure of the report remains the same. On the Desired Output tab I have expanded the first three rows of the sample report to show how I would like to the output displayed for each row.

https://www.box.com/s/0l3yzc5bewq6cbl25ob1<o></o>

Thanks in advance!

Paul

 

[hiker95]

BlackCat,

In the future when you ask for help, you should display a small sample of your actual raw data, so that you can receive a quick resolution to your request.


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).

Option Explicit
Sub ReorgDataV2()
' hiker95, 01/22/1213
' http://www.mrexcel.com/forum/excel-questions/678872-need-help-parsing-multiple-entries-single-cell.html
Dim r As Long, lr As Long, j As Long, k As Long, l As Long
Dim s1, s2, s3
Application.ScreenUpdating = False
If ActiveSheet.FilterMode = True Then ActiveSheet.ShowAllData
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 10), Chr(10)) > 0 Then
    s1 = Split(Cells(r, 10), Chr(10))
    s2 = Split(Cells(r, 11), Chr(10))
    s3 = Split(Cells(r, 12), Chr(10))
    Rows(r + 1).Resize(UBound(s1)).Insert
    Cells(r + 1, 1).Resize(UBound(s1), 9).Value = Cells(r, 1).Resize(, 9).Value
    Cells(r + 1, 13).Resize(UBound(s1)).Value = Cells(r, 13).Value
    Cells(r, 10).Resize(UBound(s1) + 1).Value = Application.Transpose(s1)
    Cells(r, 11).Resize(UBound(s2) + 1).Value = Application.Transpose(s2)
    Cells(r, 12).Resize(UBound(s3) + 1).Value = Application.Transpose(s3)
  End If
Next r
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 ReorgDataV2 macro.

 

[BlackCat]

hiker95,

Thank you again for you help. The macro works like a champ!

In the future, I will mock up a sample of data and post it to box.net.

Paul

 

[hiker95]

BlackCat,

Thanks for the feedback.

You are very welcome. Glad I could help. Come back anytime.