0

How to create in (Excel) VBA the Cartesian product of attributes values of several attributes, stored in one and the same table?

The code should deal with a dynamic number of attributes (first column) and attribute values per attribute (second column).

Input example:

  • A | A1
  • A | A2
  • A | A3
  • B | B1
  • B | B2
  • C | C1

Result:

  • A1, B1, C1
  • A1, B2, C1
  • A2, B1, C1
  • A2, B2, C1
  • A3, B1, C1
  • A3, B2, C1

A solution in Power Query for Excel is also OK for me.

For a fixed number of attributes I could store the attribute values in an array per attribute and then use nested for loops. But I am looking for a solution with a dynamic number of attributes.

This is the code I created for a fixed number of attributes. Note: filling the arrays from the Excel table isn't the problem, so I omitted that until I have a solution for the Cartesion product.

Sub GenerateItems()

Dim strAttributeValues1 As String
Dim strAttributeValues2 As String
Dim strAttributeValues3 As String

Dim arrAttributeValues1() As String
Dim arrAttributeValues2() As String
Dim arrAttributeValues3() As String

Dim strItemName As String

strAttributeValues1 = "A1,A2,A3"
arrAttributeValues1 = Split(strAttributeValues1, ",")

strAttributeValues2 = "B1,B2"
arrAttributeValues2 = Split(strAttributeValues2, ",")

strAttributeValues3 = "C1"
arrAttributeValues3 = Split(strAttributeValues3, ",")

Dim a, b, c As Integer

For a = 0 To UBound(arrAttributeValues1)
    For b = 0 To UBound(arrAttributeValues2)
        For c = 0 To UBound(arrAttributeValues3)
            strItemName = arrAttributeValues1(a) & " " & arrAttributeValues2(b) & " " & arrAttributeValues3(c)
            Debug.Print strItemName
        Next c
    Next b
Next a

End Sub
J. Vos
  • 65
  • 6
  • Note that this task can easily take **a lot** of calculation time depending on the amount of data you have. How many different `A, B, C, …` do you plan to have (estimated maximum) and how many different numbers `A1, A2, A3, …` each letter? • You should calculate first if this is possible to run in a reasonable amount of time with your data before you start coding this in VBA. – Pᴇʜ Apr 24 '19 at 08:36
  • I added to my question the code I created for a fixed number of attributes. Note: filling the arrays from the Excel table isn't the problem, so I omitted that until I have a solution for the Cartesion product. – J. Vos Apr 24 '19 at 08:44
  • The maximum number of attributes is 12. The average number of attribute values per attribuge is about 4 or 5. – J. Vos Apr 24 '19 at 08:48
  • 1
    You can find a few different solutions [here](https://stackoverflow.com/questions/31472816) – BrakNicku Apr 24 '19 at 09:44

0 Answers0