Oct 02 2020 11:17 AM
Hello!
I have six groups of numbers, each group has three unique numbers.
I am looking for a way to generate 6 number combinations of 6 groups. The idea is to have ONLY
one number from each group and generate all of the possible combinations that follow this
rule.
How can I achieve this? I am also open to a formula solution if possible.please.Thanks!
Here is a sample file:
https://1drv.ms/x/s!AoGkZUHlKui9gS-n9_sIBtJIArMX?e=KM6sVh
Oct 02 2020 12:29 PM
SolutionHere is a macro:
Sub Combinations()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim i As Long
Dim grp
grp = Range("B1:D6").Value
Dim arr(1 To 729, 1 To 6) As Long
For a = 1 To 3
For b = 1 To 3
For c = 1 To 3
For d = 1 To 3
For e = 1 To 3
For f = 1 To 3
i = i + 1
arr(i, 1) = grp(1, a)
arr(i, 2) = grp(2, b)
arr(i, 3) = grp(3, c)
arr(i, 4) = grp(4, d)
arr(i, 5) = grp(5, e)
arr(i, 6) = grp(6, f)
Next f
Next e
Next d
Next c
Next b
Next a
Application.ScreenUpdating = False
Range("H2").Resize(729, 6).Value = arr
Application.ScreenUpdating = True
End Sub
Oct 02 2020 12:56 PM
Oct 02 2020 12:29 PM
SolutionHere is a macro:
Sub Combinations()
Dim a As Long
Dim b As Long
Dim c As Long
Dim d As Long
Dim e As Long
Dim f As Long
Dim i As Long
Dim grp
grp = Range("B1:D6").Value
Dim arr(1 To 729, 1 To 6) As Long
For a = 1 To 3
For b = 1 To 3
For c = 1 To 3
For d = 1 To 3
For e = 1 To 3
For f = 1 To 3
i = i + 1
arr(i, 1) = grp(1, a)
arr(i, 2) = grp(2, b)
arr(i, 3) = grp(3, c)
arr(i, 4) = grp(4, d)
arr(i, 5) = grp(5, e)
arr(i, 6) = grp(6, f)
Next f
Next e
Next d
Next c
Next b
Next a
Application.ScreenUpdating = False
Range("H2").Resize(729, 6).Value = arr
Application.ScreenUpdating = True
End Sub