Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: "Un-flatten" rows into multiple rows

  1. #1
    New Member
    Join Date
    Apr 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "Un-flatten" rows into multiple rows

    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.

  2. #2
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

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

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  3. #3
    New Member
    Join Date
    Apr 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    Quote Originally Posted by Snakehips View Post
    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

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,583
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    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 by hiker95; Apr 25th, 2014 at 08:08 AM.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    Quote Originally Posted by hiker95 View Post
    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
    A B C
    1 ID Description Names
    2 4711 Text1 ABC,DEF,GHI
    3 5633 Text2 KLM,OPQ,RST
    4
    5
    6
    7
    8
    Sheet1



    After the macro:

    Excel 2007
    A B C
    1 ID Description Names
    2 4711 Text1 ABC
    3 4711 Text1 DEF
    4 4711 Text1 GHI
    5 5633 Text2 KLM
    6 5633 Text2 OPQ
    7 5633 Text2 RST
    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.
    I do have a solution, but thank you for your efforts. It's always good to have more options.

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,583
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    niedawow,

    Thanks for the feedback.

    You are very welcome. Glad we could help.

    And, come back anytime.
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  7. #7
    New Member
    Join Date
    Apr 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    Quote Originally Posted by hiker95 View Post
    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
    Last edited by niedawow; Apr 28th, 2014 at 06:32 AM.

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,583
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    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-q...ml#post3769209
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  9. #9
    New Member
    Join Date
    Apr 2014
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    Quote Originally Posted by hiker95 View Post
    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-q...ml#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 ..

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,583
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    6 Thread(s)

    Default Re: "Un-flatten" rows into multiple rows

    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?
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •