Split comma separated entries to new rows using Macros

Question

I currently have this data in a sheet

Col A	Col B	Col C	Column D
1	angry birds, gaming	Youtube, twitch	Google,Facebook
2	nirvana,rock,band	Music, world,Entertaiment	Twitter,Instagram, snapchat

What I want to do is split the comma separated entries in the second column and insert in new rows like below:

Col A	Col B	Col C	Col D
1	angry birds	youtube	google
1	gaming	Twitch	Facebook
2	nirvana	Music	Twitter
2	rock	World	Instagram
2	band	Entertainment	Snapchat

I already have a formula can anyone correct this for col A and B Can any one help me with Col C and col D

    Option Explicit

Const ANALYSIS_ROW As String = "B"
Const DATA_START_ROW As Long = 1

Sub ReplicateData()
    Dim iRow As Long
    Dim lastrow As Long
    Dim ws As Worksheet
    Dim iSplit() As String
    Dim iIndex As Long
    Dim iSize As Long

    'Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    With ThisWorkbook
        .Worksheets("Sheet1").Copy After:=.Worksheets("Sheet1")
        Set ws = ActiveSheet
    End With

    With ws
        lastrow = .Cells(.Rows.Count, ANALYSIS_ROW).End(xlUp).Row
    End With


    For iRow = lastrow To DATA_START_ROW Step -1
        iSplit = Split(ws.Cells(iRow, ANALYSIS_ROW).Value2, ",")
        iSize = UBound(iSplit) - LBound(iSplit) + 1
        If iSize = 1 Then GoTo Continue

        ws.Rows(iRow).Copy
        ws.Rows(iRow).Resize(iSize - 1).Insert
        For iIndex = LBound(iSplit) To UBound(iSplit)
            ws.Cells(iRow, ANALYSIS_ROW).Offset(iIndex).Value2 = iSplit(iIndex)
        Next iIndex
Continue:
    Next iRow

    Application.CutCopyMode = False
    Application.Calculation = xlCalculationAutomatic
    'Application.ScreenUpdating = True
End Sub

Probably better to do this with Get & Transform (also known as Power Query). — BigBen, Mar 19 '21 at 13:23
We are happy to help you if you can provide any code you've already tried and ask specific questions on the problems you encounter. — PeterT, Mar 19 '21 at 13:24
many many nearly exact questions are on here: https://stackoverflow.com/questions/41003866/excel-macro-split-comma-separated-entries-to-new-rows — Scott Craner, Mar 19 '21 at 13:25
https://stackoverflow.com/questions/8560718/split-comma-separated-entries-to-new-rows — Scott Craner, Mar 19 '21 at 13:26
^^^^^ Seems not coincidental that your sample data in column B matches these questions. — BigBen, Mar 19 '21 at 13:27
Seems to me that this is a homework assignment given the similarities to the other questions. — Scott Craner, Mar 19 '21 at 13:28

score 0 · Answer 1 · edited Mar 19 '21 at 13:31

0

This would be a start, i don;'t have much time to formulate a proper answer, this takes A1:A3 and puts in one word per cell.

Dim arrIn() As Variant
Dim strJoined As String
Dim arrOut() As String

arrIn = Application.Transpose(Range("a1:a3"))
strJoined = Join(arrIn, ",")
arrOut = Split(strJoined, ",")

Range("b1").Resize(UBound(arrOut) + 1, 1).Value = Application.Transpose(arrOut)

edited Mar 19 '21 at 13:31

Dharman

30,962
25
85
135

answered Mar 19 '21 at 13:26

Nathan_Sav

8,466
2
13
20

Split comma separated entries to new rows using Macros

1 Answers1