Split comma separated entries to new rows while keeping row data

Question

I do not know vba at all so I was wondering if someone could help me out with a VBA code I'm trying to create. I have bunch of data that I have. I have data in columns A to W.

I had one column that has multiple data separated out by commas like so:

Col V  |  Col W 
----   |  ----
1      |angry birds, gaming
2      |nirvana,rock,band

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

Col V|Col W
---- |----
1    |angry birds
1    |gaming
2    |nirvana
2    |rock
2    |band

Basically, I want to replicate this

Excel macro -Split comma separated entries to new rows

but I want to do this while keeping all of the data in columns A-V intact. Also note that column W can have up to 40 items that need to be separated.

Thank you very much!

Please show your attempt at modifying the code to suit your needs. Stack Overflow is not a code for me site. We will help with specific issues with existing code, but the person posing the question must show some attempt. — Scott Craner, Jan 06 '17 at 15:50

score 1 · Answer 1 · answered Jan 28 '17 at 01:50

Try it this way. Before.

CODE

Option Explicit

Const ANALYSIS_ROW As String = "K"
Const DATA_START_ROW As Long = 2

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

AFTER.

score -1 · Answer 2 · answered Jan 06 '17 at 17:49

Based on your data (and being incrementing ID's not a deal breaker, just an assumption made) you could use formula to break it down for you. This would achieve the below, where the green area is the data, yellow will form the desired output, and for ease of understanding, I've added blue "helper" columns. The formula will be as follows

C2 filled down =LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

D2 filled down =SUM($C$2:C2)

F2 =A2

F3 =IF(ROW()-1>VLOOKUP($F2,$A$2:$D$4,4,0),$F2+1,$F2) filled down

G2 =COUNTIF($F$2:F2,F2) filled down

H2 =VLOOKUP($F2,$A$2:$B$4,2,0) filled down

I2 =IF($G2=1,SUBSTITUTE($H2,",",">",1),SUBSTITUTE(SUBSTITUTE(H2,",","<",G2-1),",",">",G2-1)) filled down

J2 =IF($G2=1,1,FIND("<",$I2)) filled down

K2 =IF($G2<VLOOKUP($F2,$A$2:$D$4,3,0),FIND(">",$I2),LEN(H2)+1)-J2 filled down L2 =IF(ISERROR(H2),"--",MID(H2,J2,IF(K2>0,K2,1))) filled down

Comma count, should read segment count, apologies.

Split comma separated entries to new rows while keeping row data

2 Answers2