Results 1 to 4 of 4

Thread: Rearrange A Delimited Column Of Data In To Separate Rows With Fixed Number Of Columns

  1. #1
    Junior Member
    Join Date
    Jul 2013
    Posts
    1
    Rep Power
    0

    Rearrange A Delimited Column Of Data In To Separate Rows With Fixed Number Of Columns

    I have this seen solution http://www.excelfox.com/forum/f22/re...ata-as-is-420/ but it puts everything in one column.

    I want to split the delimited column into a fixed number of columns, specifically two.

    Example Input:

    Column A | Column B
    1 | A, B, C, D, E
    2 | F, G, H, I, K, L, M, N, O
    3 | P, Q, R, S

    Example Output:

    Column A | Column B | Column C
    1 | A | B
    1 | C | D
    1 | E
    2 | F | G
    2 | H | I
    2 | K | L
    2 | M | N
    2 | O
    3 | P | Q
    3 | R | S

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    Code:
    Sub Consolidator()
    
        Dim var As Variant, varOut As Variant
        Dim lng As Long
        Dim lngRows As Long
        Dim lngIndex As Long
        Dim lngSplit As Long
        var = Range("A1").CurrentRegion.Resize(, 2).Value2
        For lngRows = LBound(var) To UBound(var)
            lng = lng + CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
        Next lngRows
        ReDim varOut(1 To lng, 1 To 2)
        lngIndex = 1
        For lngRows = LBound(var) To UBound(var)
            lng = CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
            For lng = lngIndex To lngIndex + lng - 1
                lngSplit = lngSplit + 1
                varOut(lng, 1) = var(lngRows, UBound(var, 2) - 1)
                varOut(lng, 2) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 2) & "," & Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 1)
            Next lng
            lngIndex = lng
            lngSplit = 0
        Next lngRows
        ActiveSheet.Next.Cells(1).Resize(UBound(varOut), 2).Value = varOut
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Just realized that you wanted the output in separate columns. The above keeps the output in a single column. The below splits it in to two columns

    Code:
    Sub Consolidator()
    
        Dim var As Variant, varOut As Variant
        Dim lng As Long
        Dim lngRows As Long
        Dim lngIndex As Long
        Dim lngSplit As Long
        var = Range("A1").CurrentRegion.Resize(, 2).Value2
        For lngRows = LBound(var) To UBound(var)
            lng = lng + CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
        Next lngRows
        ReDim varOut(1 To lng, 1 To 3)
        lngIndex = 1
        For lngRows = LBound(var) To UBound(var)
            lng = CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
            For lng = lngIndex To lngIndex + lng - 1
                lngSplit = lngSplit + 1
                varOut(lng, 1) = var(lngRows, UBound(var, 2) - 1)
                varOut(lng, 2) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 2)
                varOut(lng, 3) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 1)
            Next lng
            lngIndex = lng
            lngSplit = 0
        Next lngRows
        ActiveSheet.Next.Cells(1).Resize(UBound(varOut), 3).Value = varOut
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Well actually, I thought the first column contains index numbers, 1,2,3 etc. But it seems that was just you showing that the data started from Row 1

    So you can use the following lines of code instead of the ones above.

    Code:
    Sub Consolidator()
    
        Dim var As Variant, varOut As Variant
        Dim lng As Long
        Dim lngRows As Long
        Dim lngIndex As Long
        Dim lngSplit As Long
        var = Range("A1").CurrentRegion.Resize(, 1).Value2
        For lngRows = LBound(var) To UBound(var)
            lng = lng + CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
        Next lngRows
        ReDim varOut(1 To lng, 1 To 2)
        lngIndex = 1
        For lngRows = LBound(var) To UBound(var)
            lng = CLng((UBound(Split(var(lngRows, UBound(var, 2)), ",")) + 2) / 2)
            For lng = lngIndex To lngIndex + lng - 1
                lngSplit = lngSplit + 1
                varOut(lng, 1) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 2)
                varOut(lng, 2) = Split(var(lngRows, UBound(var, 2)) & ",", ",")(lngSplit * 2 - 1)
            Next lng
            lngIndex = lng
            lngSplit = 0
        Next lngRows
        ActiveSheet.Next.Cells(1).Resize(UBound(varOut), 2).Value = varOut
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 34
    Last Post: 03-13-2015, 02:26 PM
  2. Move data from rows into columns for every unique value
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-13-2013, 03:02 AM
  3. Replies: 4
    Last Post: 05-01-2013, 09:49 PM
  4. Replies: 3
    Last Post: 03-16-2013, 05:13 PM
  5. Converge Data From Multiple Columns To Single Column
    By ayazgreat in forum Excel Help
    Replies: 3
    Last Post: 12-14-2012, 10:55 PM

Tags for this Thread

Posting Permissions

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