Insert copied row in excel based on row's cell data, then populate cells based on parts of row's referenced cell

Question

Can anyone make sense of what I am trying to do? This is the best way I can think to explain it in words. Insert row in excel based on cell data, then copy previous row data to the new row and then populate cell from parts of the original row's referenced cell data.

Here is an image before:

And here is an image of the result:

I don't know if this can be done with VBA/Macro, a built-in formula etc.

Another way to describe my manual process is I start with row 2, if there is a value in C2 then I insert a new row for however many values are in C2 separated by a comma. I then copy contents from A2 & B2 into the new blank rows 3 & 4, then I separate the values from C2 and paste them into C2 C3, C4. If a row doesn't have a value in the C column then I move onto the next row and so on until I reach a row in C column that has a cell value and repeat the method from above.

Answer 1

In your workbook, create a new worksheet called "Destination". Copy and paste the below code into a new module within VBA ...

Public Sub SplitRowsBasedOnLastColumn()
    Dim rngCells As Range, lngRow As Long, lngCol As Long, strLastColValue As String, i As Long
    Dim strDelimiter As String, objDestSheet As Worksheet, lngWriteRow As Long, arrValues

    Set rngCells = Selection
    strDelimiter = ","

    Set objDestSheet = Sheets("Destination")
    lngWriteRow = 1

    With rngCells
        objDestSheet.Cells.Clear

        For lngCol = 1 To .Columns.Count
            objDestSheet.Cells(1, lngCol) = .Cells(1, lngCol)
        Next

        For lngRow = 2 To .Rows.Count
            strLastColValue = .Cells(lngRow, .Columns.Count)

            If strLastColValue = "" Then strLastColValue = " "

            arrValues = Split(strLastColValue, strDelimiter)

            For i = 0 To UBound(arrValues)
                lngWriteRow = lngWriteRow + 1

                For lngCol = 1 To .Columns.Count - 1
                    objDestSheet.Cells(lngWriteRow, lngCol) = .Cells(lngRow, lngCol)
                Next

                objDestSheet.Cells(lngWriteRow, .Columns.Count) = Trim(arrValues(i))
            Next
        Next
    End With
End Sub

Now select your range of data including headers (as shown) and then run the macro.

After it's done, check the "Destination" sheet and you should have your result.

I wrote this based off the data set you provided, it may need tweaks if you have any scenarios outside of that.

Let me know how it goes.