"Un-flatten" rows into multiple rows

[niedawow]

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.

 

[hiker95]

niedawow,

The following macro will find the last used column in the worksheet.

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

Sub ReorgDataV3()
' hiker95, 04/28/2014, ME773426
Dim r As Long, lr As Long, lc As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
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), lc) = Cells(r, 7).Resize(, lc).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 ReorgDataV3 macro.

 

[niedawow]

Sorry about that. Will keep it clean.

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

Yes, row 1 is the title. Rows are being created correctly, but with data being populated only up to column 9.

 

[hiker95]

niedawow,

See my reply #11 for the latest macro.

 

[niedawow]

niedawow,

See my reply #11 for the latest macro.

Yes, you have replied before I got to refresh the thread. The macro works on my crafted work sheet just fine. For testing purposes I created data in columns M and AF. The macro went through all of them and populated the data in the new rows.

Thanks, hiker95 Great work.

 

[Rick Rothstein]

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

One more for you to consider...

<!-- title / author block -->Redistribute a Delimited Column Of Data into Separate Rows (Keeping Other Data As Is)

 

[hiker95]

niedawow,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[niedawow]

I just tested the macro on a copy of my production data and it's working flawlessly, so again - the help is very much apreciated.

Now I am thinking in terms of how to optimize the whole workflow.

Right now:

1. Download sheets via URL links (hard URLs which could be used as variables)
2. Open sheets
3. Inject and run macro
4. Save sheets
5. Import into MS Access DB

For me, it would be a great relief, if I could automate steps 1-4. My search ended up with scenarios where the macros are already in the sheets. My sheets are generated reports from some system. Any pointers?

 

[hiker95]

niedawow,

Thanks for the feedback.

You are very welcome. Glad I could help.

Right now:

1. Download sheets via URL links (hard URLs which could be used as variables)
2. Open sheets
3. Inject and run macro
4. Save sheets
5. Import into MS Access DB

For me, it would be a great relief, if I could automate steps 1-4.

You have not told us what version of Excel and Windows you are using.

You could record a macro to do steps 1 and 2.

You could then combine your import macro and my macro, and, place them in your Personal.xls or Personal.xlsb workbook and automate steps 1 thru 4.


I have no experience with step 5.

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.

 

[niedawow]

Hello hiker95 and everybody,

I need to bump this thread, because I have experienced a problem while running this macro (Windows 7, Excel 2010).

I refer to the V3 of hiker95:

Sub ReorgDataV3()
' hiker95, 04/28/2014, ME773426
Dim r As Long, lr As Long, lc As Long, s
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 1).End(xlUp).Row
lc = Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
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), lc) = Cells(r, 7).Resize(, lc).Value
  End If
Next r
Columns(6).AutoFit
Application.ScreenUpdating = True
End Sub

Some of my tables have columns with release dates and so on. When I run the macro some of the dates are being altered.

Original (dates are in 4 columns):
2014-02-26 2014-09-30 2014-10-01 2014-10-01
2014-02-26 2014-09-30 2014-10-01 2014-10-01

Altered (dates are in 4 columns):
2014-02-26 2014-09-30 2014-10-01 2014-10-01
26.02.2014 30.09.2014 01.10.2014 01.10.2014

Basicly, it switches the date format on what it seems random rows.

Any idea how to correct this?

Thanks

 

[hiker95]

niedawow,

Some of my tables have columns with release dates and so on. When I run the macro some of the dates are being altered.

Your text display is not very helpful.

In order to continue I will have to see your actual raw data worksheet(s).

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.com | Excel Resources | Excel Seminars | Excel Products

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.