[FIXED] How to insert multiple records based on two columns of comma-separated values

Issue

I'm trying to create records based on multiple cells with comma delimited values.

Take This:

    Handle      |     Title    |   Color    |   Size     |     Price 
                |              |            |            |  (0-04/06-08)
---------------------------------------------------------------------------
Jovani-JVN86957 |     Jovani   |  Black,    | 0, 02,     |   $199 (if size > 06  then +15)
                     JVN86957  | Red, White | 04, 06, 08 |     

What I'm trying to do is have the Color and Size columns generate new records while including the Handle and Price(also if possible but not necessary to have the price changes based on the Size value ie: size 0-4 = $199 and size 6-8 = $219)

Turn to this:

Handle                Title         Color    Size  Price(0 04/06 08 +$15)

Jovani-JVN86957   Jovani-JVN86957   Black    0        $199
Jovani-JVN86957                     Black    2        $199
Jovani-JVN86957                     Black    4        $199
Jovani-JVN86957                     Black    6        $199 +$15 = $214
Jovani-JVN86957                     Black    8        $199 +$15 = $214
Jovani-JVN86957                     Red      0        $199
Jovani-JVN86957                     Red      2        $199
Jovani-JVN86957                     Red      4        $199
Jovani-JVN86957                     Red      6        $199 +$15 = $214
Jovani-JVN86957                     Red      8        $199 +$15 = $214
Jovani-JVN86957                     White    0        $199
Jovani-JVN86957                     White    2        $199
Jovani-JVN86957                     White    4        $199
Jovani-JVN86957                     White    6        $199 +$15 = $214
Jovani-JVN86957                     White    8        $199 +$15 = $214

Any help would be much appreciated.

Solution

Here is one method that is a bit more of a brute force approach by using multiple loops.

Sub SplitEntries()
    Dim rng As Range
    Set rng = Range("A2:A5")

    'Copy over header
    Range("G1:K1").Value = Range("A1:E1").Value

    Dim sizeSplit As Variant
    Dim colorSplit As Variant
    Dim rowCntr As Integer: rowCntr = 2

    'Loop thru range
    For Each cell In rng
        Range("H" & rowCntr).Value = cell.Offset(0, 1).Value

        'Get all the colors
        colorSplit = Split(cell.Offset(0, 2), ",")

        'Loop through each color
        For i = LBound(colorSplit) To UBound(colorSplit)
            'Get all the sizes
            sizeSplit = Split(cell.Offset(0, 3), ",")

            'Loop through each size
            For j = LBound(sizeSplit) To UBound(sizeSplit)
                Range("G" & rowCntr).Value = cell.Value
                Range("I" & rowCntr).Value = Trim(colorSplit(i))
                Range("J" & rowCntr).Value = Trim(sizeSplit(j))
                'Range("K" & rowCntr).Value = cell.Offset(0, 4).Value

                If Trim(sizeSplit(j)) < 6 Then
                   Range("K" & rowCntr).Value = cell.Offset(0, 4).Value
                Else
                   Range("K" & rowCntr).Value = cell.Offset(0, 4).Value + 15
                End If

                rowCntr = rowCntr + 1
            Next j
        Next i
    Next cell
End Sub

---

The starting data looks like this:

And the results are put in new columns like this:

Answered By - Automate This

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)