Use Excel/VB to create a list of product variation

Question

I am running a Woocommerce shop with the product CSV import suit plugin. I need to first upload a list with parent products and second a list with products variations. Creating the second list is quite labor intensive, so I am looking for a way to automatically generate this list as much as possible.

Here is how a short, simplified list of parent products could look like:

The actual list with parent products would contain many more product characteristics.

And this is how the results with product variations should look like:

As you see the can see the product variation (child) inherents all of the products characteristics of the parent product and only one of the size and color variables. It would be nice if a child sku would automatically be generated as well.

I would like the output data (product variations) to be generated in a different worksheet (seperate from the parent product list).

Does anyone have any thoughts how to do this in Excel? I am no Excel wizard, so I hope you can give me some guidance on how to implement your solution.

Answer 1

The logic here is to read each row until an empty row is found, and then split the colors and sizes. Then create an outer loop for the colors and inner loop for the sizes (this will process all variations). You will also need a variable to hold the child sku counter and reset it when the variation are done for a product. The following will get you started:

Sub ProductVariations()

Dim productRow As Long, variationRow As Long
Dim size As String
Dim color As String
Dim skuCounter As Integer
Dim skuChild As String
Dim sizeArray() As String
Dim colorArray() As String
Dim sizeElement As Variant
Dim colorElement As Variant
Dim productsSheet As Worksheet
Dim variationsSheet As Worksheet

productRow = 2
variationRow = 2

Set productsSheet = ActiveWorkbook.Sheets(1)
Set variationSheet = ActiveWorkbook.Sheets(2)

'loop through each row until a blank row is found
While productsSheet.Cells(productRow, 1).Value <> ""

   'get the size and color values for the current row
   size = productsSheet.Cells(productRow, 3)
   color = productsSheet.Cells(productRow, 4)

   'make sure a size and color exists then process
   If Len(size) > 0 And Len(color) > 0 Then

       'reset the sku counter
       skuCounter = 0

       'split the size and color into arrays
       sizeArray = Split(size, "|")
       colorArray = Split(color, "|")

       'loop through each size
       For Each sizeElement In sizeArray

           'loop through each color
           For Each colorElement In colorArray

               'increment the child counter for this variation
               skuCounter = skuCounter + 1

               'set the appropriate cells in the variations sheet (you have some more work to do here
               skuChild = productsSheet.Cells(productRow, 2).Value & "-" & skuCounter

               variationSheet.Cells(variationRow, 1).Value = productsSheet.Cells(productRow, 1)
               variationSheet.Cells(variationRow, 3).Value = skuChild

               'increment the variation row so the next variation goes in the next row
               variationRow = variationRow + 1

           Next colorElement

       Next sizeElement

   End If

   'very important increment the next row or only the first row will ever get processed
   productRow = productRow + 1
Wend 
End Sub