Combine Product List and Attribute List

Hello Excel Forum,

I'm new to VBA and the forum, but I have a task in Excel that I'm looking for some wisdom about how to approach. I want to take data from a list of products, and data from a list of additional attributes of these products, then combine the data into a table where each discrete product has its own row.

My example below is a simplified version of what I want to do. I hope it helps to understand my question. My real data is much larger and more complex. Does anyone have a good idea about how to approach something like this?

Thank you.
Stasi
Sample.xlsx

example1.png

Please attach an Excel sample file to help for VBA

I updated my original post to include a sample file.

Clear thank you

Are "Input Table" and "Additional Parameters" table always with the same criterias and nb of rows ?

Are th number of columns always the same:
Material 1" length price 1.5" length price 2" length price
Pitch Head Style Length

Yes, for the input table, the number of columns will always be the same.

and for "Additional Parameters" the same : Pitch Head Style Length

The key between "Input Table" and "Additional Parameters" is still the "Length" ?

Today in your "Output Table" the criteria order is:
1 - Material
2 - Length
3 - Head Style
4 - Pitch
Is it always the same ?

To make it easy I suggast to change column order in "Outout"
"Material Length Head Stylle Pitch" ... and others to come and at the end "Price id"
is it OK ?

See here what could be done.
Have a look on the file attached to see adjustments done

Option Explicit

Sub Man()
Const InTabAdd = "A2"
Const AssParTab = "F2"
Dim I1 As Integer, I2 As Integer
Dim Rg1 As Range, Rg2 As Range, Rg3 As Range, Rg4 As Range
Dim R3 As Range, R4 As Range
Set Rg1 = Range(Cells(3, "A"), Cells(3, "A").End(xlDown))
Set Rg2 = Range(Cells(3, "F"), Cells(3, "F").End(xlDown))
Set Rg3 = Range(Cells(3, "G"), Cells(3, "G").End(xlDown))
Set Rg4 = Range(Cells(3, "H"), Cells(3, "H").End(xlDown))
Dim II As Integer
Dim InRg As Range
Dim LR As Integer, LC  As Integer
Dim Price As Single
Dim OutWs As Worksheet
Set OutWs = Sheets("Output")
Dim id As String
    LC = Range(InTabAdd).End(xlToRight).Column
    LR = Range(InTabAdd).End(xlDown).Row
    Set InRg = Range(Range(InTabAdd), Cells(LR, LC))
    
    II = 1
    With OutWs
        .Cells.ClearContents
        .Cells(II, 1).Resize(1, 6) = Array(Rg1(0, 1), Rg2(0, 1), Rg3(0, 1), Rg4(0, 1), "Price", "id"): II = II + 1
        For I1 = 1 To Rg1.Rows.Count
            For I2 = 1 To Rg2.Rows.Count
                For Each R3 In Rg3
                    For Each R4 In Rg4
                        Price = Application.Index(InRg, I1 + 1, I2 + 1)
                        id = "#" & Format(II - 1, "0000")
                        .Cells(II, 1).Resize(1, 6) = Array(Rg1.Cells(I1, 1), Rg2.Cells(I2, 1), R3, R4, Price, id): II = II + 1
                    Next R4
                Next R3
            Next I2
        Next I1
    End With
End Sub

Yes, test and retest
Thx for the Rep.