VBA - Output all possible combinations of data sets when grouped by data in Column A

Question

I decided to start this question from scratch to clarify the question and goal.

Things to note about my data

• I have a spreadsheet containing laptop product data
• Each product has a Model and a SKU value
• Many models have multiple SKUs associated with them
• When more than 1 SKU fits into a model, there is a new row for each SKU. In this instance, each row will have the same value in the model field
• Some models may have 4 batteries & 1 charger, others may have 1 battery & 2 chargers, others may have 1 battery & no charger or vice versa... what i'm trying to say is there is no set rule or relation between number of SKUs
• There are two types of products, batteries & chargers
• All Battery products have SKUs that begin with 'BAT'
• All charger products have SKUS that begin with either 'ACA' or 'ACS'
• I can easily split the two types of data to help achieve the goal - SKU, model & category data can be placed side by side in columns or in separate worksheets for each type of product (battery or charger)

Example data formatted side by side in same worksheet:

Example data in separate worksheets (sheet1 = batteries, sheet2 = chargers):

Regardless of which method is used, the model field could be positioned anywhere in column A - the model field will not be in an adjacent cell when comparing the two sets of data (as illustrated)

What I am trying to achieve

• For each model, I need to have a row of data containing a battery SKU and a charger SKU
• There should be a new row for the same model until all combinations are output for that model
• There should be a maximum of 2 SKUs in the output for each row. This should always contain 1 battery and 1 charger

Desired Output

It is worth mentioning that this is a very small sample of the data I will be working with, full data set is more than 60k rows and growing so the solution would need to be efficient.

I'm using excel 2007.

I am a complete noob with VBA, I have purchased some plug ins to try and achieve my goal, I have spent 2 days researching and trying various methods to do this but all to no avail.

I thought that I had got close with this answer from Santosh:
https://stackoverflow.com/a/19780188/1018153
Which is what I based my previous question on, but as well as that producing duplicates and matching data between models, I couldn't actually format my data in it's complete form for that script to work for me anyway, so my original question was irrelevant.

Answer 1

The below statement should still work, but I wrote code to try to explain how it would work

Option Explicit 'This ensures typos in variable names are flagged

Sub MakeList()
Dim BatteryList As Range
Dim ChargerList As Range
Dim CurrentModel As String
Dim i As Long
Dim j As Long
Dim k As Long

Dim resultrange As String

'look at the lists - note I am not looking at the type - I'm going to assume
'that we can set the address correctly

'use End(xLdown) to find the last cell - that way we don't need to
'remember to change it when the number of items changes
Set BatteryList = Worksheets("Sheet1").Range("A2", Range("sheet1!B1").End(xlDown))
Set ChargerList = Worksheets("Sheet2").Range("A2", Range("Sheet2!B1").End(xlDown))
'note the use of the Sheet2! and sheet1! in the .End(xlDown) - this is required
'even though we have the Worksheets(" to set the range

i = 2 ' result row
For j = 1 To BatteryList.Rows.Count ' look at each battery row
    CurrentModel = BatteryList(j, 1)
    For k = 1 To ChargerList.Rows.Count 'then look at each charger row
        If ChargerList(k, 1) = CurrentModel Then
            'and only write a row if the battery and charger models match
            Worksheets("Sheet3").Cells(i, 1) = CurrentModel
            Worksheets("Sheet3").Cells(i, 2) = BatteryList(j, 2)
            Worksheets("Sheet3").Cells(i, 3) = ChargerList(k, 2)
            i = i + 1
        End If
    Next k
Next j

End Sub

---

PreviousAnswer

---

Looking at the code in the question you pointed to, you would need to store the current model, and only add in the possibilities when the model matches. This will result in lots of #N/A! 's when the data is written out, but that should be a minor fix.

at this line:

 Do While j <= UBound(c1)

I would insert the code to hold the current model

 Dim OnlyThisModel as string
 Do While j <= UBound(c1)
     OnlyThisModel=c1(j,1)

and in this area

            Do While m <= UBound(c4)
                out(n, 1) = c1(j, 1)
                out(n, 2) = c2(k, 1)
                out(n, 3) = c3(l, 1)
                out(n, 4) = c4(m, 1)
                n = n + 1
                m = m + 1
            Loop

Check that the model is correct, and don't write if not:

            Do While m <= UBound(c4)
                if c1(j,1)=OnlyThisModel then
                    'Only write out data if model matches
                    out(n, 1) = c1(j, 1)
                    out(n, 2) = c2(k, 1)
                    out(n, 3) = c3(l, 1)
                    out(n, 4) = c4(m, 1)
                    n = n + 1
                end if
                'go to next record, regardless of if a combination was written
                m = m + 1
            Loop