CODEWITHSUNDEEP
excelvbasplitduplicates
Matt
Matt

Reputation: 15

Excel 2010 split cell and duplicate row

I know similar questions have been answered but they did not work for me. I am not an expert with excel so I could use some help.

I have data in columns A-P but in columns I and M there is data separated by a semi-colon. I need to split both columns I and M and make duplicated rows with all the other data. I would imagine that I have to split I and make duplicate rows and then repeat for M but I'm not sure.

Sorry again for asking a similar question.

Upvotes: 1

Views: 884

Answers (2)

ASH
ASH

Reputation: 20332

According to your description, this should do what you want.

Option Explicit

'Qual coluna possui os termos que repetem?
Const ANALYSIS_ROW1 As String = "I"
Const ANALYSIS_ROW2 As String = "M"

'Em qual linha começam os dados?
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_ROW1).End(xlUp).Row
    End With


    For iRow = LastRow To DATA_START_ROW Step -1
        iSplit = Split(ws.Cells(iRow, ANALYSIS_ROW1).Value2, ";")
        iSplit = Split(ws.Cells(iRow, ANALYSIS_ROW2).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_ROW1).Offset(iIndex).Value2 = iSplit(iIndex)
            ws.Cells(iRow, ANALYSIS_ROW2).Offset(iIndex).Value2 = iSplit(iIndex)
        Next iIndex
Continue:
    Next iRow

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

Upvotes: 2

VBobCat
VBobCat

Reputation: 2712

I would:

  1. Insert two columns after I and use formulas below (example for line 2):

  2. Set cells in J with this formula: =LEFT(I2;SEARCH(";";I2)-1)

  3. Set cells in K with this formula: =MID(I2;SEARCH(";";I2)+1;10000)

  4. Copy the two new columns' content and paste special as values in the same place.

  5. Finally delete column I.

...And do the same for column M

You could always write a macro to iterate rows, read values, split them with a Split() function, write the new values in new columns, but I don't think it is worth the effort.

Upvotes: 0

Related Questions