+ Reply to Thread
Results 1 to 7 of 7

Creating multiple rows from single row of data

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    03-26-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2013
    Posts
    128

    Creating multiple rows from single row of data

    I have a list of data that has information from column D that is put together with "_". I need to separate this data and create new rows for each row of data with all the same information in columns A:C but with each individual character in column D.

    I have attached a spreadsheet that will show the before and after for what the data needs to look like.
    Creating new rows with methods question.xlsx

    Help is greatly appreciated!

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Creating multiple rows from single row of data

    Hi RaydenUK,
    try this
    Sub ertert()
    Dim x, y(), sp, i&, j&, k&
    x = Sheets("Before").Range("A1").CurrentRegion.Value
    ReDim y(1 To 4, 1 To UBound(x))
    For i = 1 To UBound(x)
        sp = Split(x(i, 4), "_")
        For j = LBound(sp) To UBound(sp)
            k = k + 1: If k > UBound(y, 2) Then ReDim Preserve y(1 To 4, 1 To UBound(y, 2) * 1.5)
            y(1, k) = x(i, 1)
            y(2, k) = x(i, 2)
            y(3, k) = x(i, 3)
            y(4, k) = sp(j)
        Next j
    Next i
    With Sheets("After")
        .UsedRange.ClearContents
        .Range("A1:D1").Resize(k).Value = Application.Transpose(y)
        .Activate
    End With
    End Sub

  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Creating multiple rows from single row of data

    Sub Rayden()
    Dim ws1 As Worksheet:   Set ws1 = Sheets("Before") 'source sheet
    Dim ws2 As Worksheet:   Set ws2 = Sheets("After") 'destination sheet
    Dim c As Range
    Dim v As Variant
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
    For Each c In ws1.Range("D2:D" & ws1.Range("D" & Rows.Count).End(xlUp).Row)
        If Not InStr(1, c, "_") = 0 Then
            v = Split(c, "_")
            For i = LBound(v) To UBound(v)
                ws1.Range("A" & c.Row, "C" & c.Row).Copy ws2.Range("A" & Rows.Count).End(3)(2)
                ws2.Range("D" & Rows.Count).End(3)(2) = v(i)
            Next i
        Else
            c.Offset(, -3).Resize(1, 4).Copy ws2.Range("A" & Rows.Count).End(3)(2)
        End If
    Next c
    
    Application.ScreenUpdating = True
    
    End Sub

  4. #4
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Creating multiple rows from single row of data

    Yet another approach:

    Sub RaydenUK()
    
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
    For i = LastRow To 2 Step -1
    Methods = Cells(i, 4).Value
    If InStr(1, Methods, "_") Then
        For j = UBound(Split(Methods, "_")) To 0 Step -1
        Rows(i + 1).EntireRow.Insert
        Rows(i).Copy Rows(i + 1)
        Cells(i + 1, 4) = Split(Methods, "_")(j)
        Next
    Rows(i).EntireRow.Delete
    End If
    Next
    
    End Sub

  5. #5
    Forum Contributor
    Join Date
    03-26-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Creating multiple rows from single row of data

    Ha! Thanks everyone! I actually tried all of these and they work perfect. If I may ask though, what would the code look like to do the reverse? I ask because I have to alter the methods once they are in their own columns, but then I have to put them back into their original format like they started. I really appreciate the help!

  6. #6
    Forum Expert
    Join Date
    08-28-2014
    Location
    Texas, USA
    MS-Off Ver
    2016
    Posts
    1,796

    Re: Creating multiple rows from single row of data

    Reverse:

    Sub RaydenUK_2()
    
    i = 2
    Do
        Acc = Cells(i, 1).Value
        If Cells(i + 1, 1).Value <> Acc Then
            Acc = Cells(i + 1, 1).Value
            i = i + 1
        Else
            Cells(i, 4).Value = Cells(i, 4).Value & "_" & Cells(i + 1, 4).Value
            Rows(i + 1).EntireRow.Delete
        End If
    Loop While Cells(i, 1).Value <> ""
    
    End Sub

  7. #7
    Forum Contributor
    Join Date
    03-26-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Creating multiple rows from single row of data

    Thank you walruseggman that works perfect! I am very grateful for everyone's help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 08-20-2014, 05:46 AM
  2. Replies: 6
    Last Post: 02-19-2014, 10:25 AM
  3. Replies: 5
    Last Post: 05-25-2013, 07:12 AM
  4. Creating Multiple rows from a single row of multiple columns
    By n3mcx1 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-14-2012, 09:38 PM
  5. Replies: 1
    Last Post: 03-18-2009, 04:18 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1