"Un-flatten" rows into multiple rows

niedawow

New Member
Joined
Apr 25, 2014
Messages
10
Hello,

In my current project I have a couple of very long sheets with similar data that I need to unflatten.

Example (row "Name" being with multiple records separated by comma):

ID Description Names
4711 Text1 ABC,DEF,GHI
5633 Text2 KLM,OPQ,RST

Needs to be transformed into:

ID Description Names
4711 Text1 ABC
4711 Text1 DEF
4711 Text1 GHI
5633 Text2 KLM
5633 Text2 OPQ
5633 Text2 RST

The new rows need to be inserted prior to converting the next row of the original sheet. Is there a formula I can use to do that? Or could someone be so kind to craft a macro I can run automatically through the sheet?

Thanks in advance.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
niedawow,

Welcome to MrExcel.

Assuming that row 1 has headers and that Names is column C and are separated by comma then try this code....

Code:
Sub Un_Flatten()
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row  'assumes data in rows A:C
For i = lastrow To 2 Step -1 'Assumes row 1 has headers
Arry = Split(Trim(Range("C" & i)), ",")
c = UBound(Arry)
Range("C" & i).Resize(c, 1).EntireRow.Insert
With Range("C" & i)
For r = 0 To c
.Offset(r, 0) = Arry(r)
.Offset(r, -1) = .Offset(c, -1)
.Offset(r, -2) = .Offset(c, -2)
Next r
End With
Next i
End Sub

Hope that helps.
 
Upvote 0
niedawow,

Welcome to MrExcel.

Assuming that row 1 has headers and that Names is column C and are separated by comma then try this code....

Code:
Sub Un_Flatten()
Application.ScreenUpdating = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row  'assumes data in rows A:C
For i = lastrow To 2 Step -1 'Assumes row 1 has headers
Arry = Split(Trim(Range("C" & i)), ",")
c = UBound(Arry)
Range("C" & i).Resize(c, 1).EntireRow.Insert
With Range("C" & i)
For r = 0 To c
.Offset(r, 0) = Arry(r)
.Offset(r, -1) = .Offset(c, -1)
.Offset(r, -2) = .Offset(c, -2)
Next r
End With
Next i
End Sub

Hope that helps.

Thank you so much for your help. That works like a charm :)
 
Upvote 0
niedawow,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:


Excel 2007
ABC
1IDDescriptionNames
24711Text1ABC,DEF,GHI
35633Text2KLM,OPQ,RST
4
5
6
7
8
Sheet1


After the macro:


Excel 2007
ABC
1IDDescriptionNames
24711Text1ABC
34711Text1DEF
44711Text1GHI
55633Text2KLM
65633Text2OPQ
75633Text2RST
8
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
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.

Code:
Sub ReorgData()
' hiker95, 04/25/2014, ME773426
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 3), ",") Then
    s = Split(Cells(r, 3), ",")
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r, 3).Resize(UBound(s) + 1) = Application.Transpose(s)
    Cells(r + 1, 1).Resize(UBound(s), 2) = Cells(r, 1).Resize(, 2).Value
  End If
Next r
Columns(3).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.


I see that you already have a macro solution.
 
Last edited:
Upvote 0
niedawow,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:

Excel 2007
ABC
1IDDescriptionNames
24711Text1ABC,DEF,GHI
35633Text2KLM,OPQ,RST
4
5
6
7
8

<tbody>
</tbody>
Sheet1



After the macro:

Excel 2007
ABC
1IDDescriptionNames
24711Text1ABC
34711Text1DEF
44711Text1GHI
55633Text2KLM
65633Text2OPQ
75633Text2RST
8

<tbody>
</tbody>
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
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.

Code:
Sub ReorgData()
' hiker95, 04/25/2014, ME773426
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 3), ",") Then
    s = Split(Cells(r, 3), ",")
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r, 3).Resize(UBound(s) + 1) = Application.Transpose(s)
    Cells(r + 1, 1).Resize(UBound(s), 2) = Cells(r, 1).Resize(, 2).Value
  End If
Next r
Columns(3).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.


I see that you already have a macro solution.

I do have a solution, but thank you for your efforts. It's always good to have more options.
 
Upvote 0
niedawow,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.
 
Upvote 0
niedawow,

Thanks for the feedback.

You are very welcome. Glad we could help.

And, come back anytime.

Thanks, hiker95. I am taking your offer sooner as intended. The first batch of tables were simple and consistent enough. Now I have another batch of data which is a bit more complicated.

Original data:

Things1 Things2 ID Description SomeStuff Names EvenMoreStuff1 EvenMoreStuff2 EvenMoreStuffn
Heh1 Heh2 4711 Text1 Yolo1 ABC Hippi1 Hippi2 HippiN
Heh1 Heh2 5633 Text2 Yolo1 KLM,OPQ,RST Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 KLM,OPQ,RST,JCN,NMP,WER,FGH,DSA Hippi1 Hippi2 HippiN

What I need:

Basicly the same thing. The macro needs to look up in row F (Names, which are not always 3, but random number) and count how many names there are (e.g. KLM,OPQ,RST=3). For each of these names it needs to create a new row with the data of ALL other colums, but with one name only.

Heh1 Heh2 4711 Text1 Yolo1 ABC Hippi1 Hippi2 HippiN
Heh1 Heh2 5633 Text2 Yolo1 KLM Hippi1 Hippi2 HippiN
Heh1 Heh2 5633 Text2 Yolo1 OPQ Hippi1 Hippi2 HippiN
Heh1 Heh2 5633 Text2 Yolo1 RST Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 KLM Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 OPQ Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 RST Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 JCN Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 NMP Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 WER Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 FGH Hippi1 Hippi2 HippiN
Heh1 Heh2 9988 Text2 Yolo1 DSA Hippi1 Hippi2 HippiN

I guess the macros here always count up to 3 names.

Could someone please adjust the macros for them to be able to handle random length of the column with the names?

Thanks in advance!

P.S. It would be nice, if you take a couple of minutes time to comment important lines in the macro. I like to experiment by myself, but am not familiar with the macro language :rolleyes:
 
Last edited:
Upvote 0
niedawow,

Thanks, hiker95. I am taking your offer sooner as intended. The first batch of tables were simple and consistent enough. Now I have another batch of data which is a bit more complicated.

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

Code:
Sub ReorgDataV2()
' hiker95, 04/28/2014, ME773426
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 6), ",") Then
    s = Split(Cells(r, 6), ",")
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r, 6).Resize(UBound(s) + 1) = Application.Transpose(s)
    Cells(r + 1, 1).Resize(UBound(s), 5) = Cells(r, 1).Resize(, 5).Value
    Cells(r + 1, 7).Resize(UBound(s), 3) = Cells(r, 7).Resize(, 3).Value
  End If
Next r
Columns(6).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 ReorgDataV2 macro.


P.S. It would be nice, if you take a couple of minutes time to comment important lines in the macro. I like to experiment by myself, but am not familiar with the macro language

I will be back later after I go fly fishing.


In the mean time, check of the below link for my most up to date list for training:

Training / Books / Sites as of 4/02/2014
http://www.mrexcel.com/forum/excel-...-row-right-up-multiple-times.html#post3769209
 
Upvote 0
niedawow,



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

Code:
Sub ReorgDataV2()
' hiker95, 04/28/2014, ME773426
Dim r As Long, lr As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
For r = lr To 2 Step -1
  If InStr(Cells(r, 6), ",") Then
    s = Split(Cells(r, 6), ",")
    Rows(r + 1).Resize(UBound(s)).Insert
    Cells(r, 6).Resize(UBound(s) + 1) = Application.Transpose(s)
    Cells(r + 1, 1).Resize(UBound(s), 5) = Cells(r, 1).Resize(, 5).Value
    Cells(r + 1, 7).Resize(UBound(s), 3) = Cells(r, 7).Resize(, 3).Value
  End If
Next r
Columns(6).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 ReorgDataV2 macro.




I will be back later after I go fly fishing.


In the mean time, check of the below link for my most up to date list for training:

Training / Books / Sites as of 4/02/2014
http://www.mrexcel.com/forum/excel-...-row-right-up-multiple-times.html#post3769209

Thank you so much, hiker95. It works (almost) perfectly. The only error I found is that after the macro creates the new rows it copies the data from the first 9 colums (up to column I, included) only and leaves the rest blank. My sheets have sometimes more than 40 columns of data. I need the data from every single column in the newly created rows. How can I correct this behaviour?

Don't worry about the comments. It's exactly this kind of situation where I could alter the script by myself, if I would understand exactly how it works. Yet, free time is almost non-existant ..
 
Upvote 0
niedawow,

Please do not quote entire replies from your helper. When quoting follow these guidelines:

1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


My sheets have sometimes more than 40 columns of data.

1. Is your title row in the above mentioned sheets always row 1?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

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
Back
Top