Using Excel 2010
Hello,
The macro below creates combinations of 5 numbers out of 1st 4 groups. (Group1, Group 2, Group 3 and Group 4…are shown in yellow)
But now I require modifications so it can create all possible combinations of 5 numbers using 6 groups.... From Group1 to Group6 (including 2 blue groups 5 and 6 also)
Please see the attached example below….
I tried to change (arrData = Range("A2:D4").Value ' Change here) To (arrData = Range("A2:F4").Value ' Change here) but seems it is design to create combinations only from 4 groups.
Please need help to modify the existing VBA; which cam work with present request. I want which can work with Excel version 2000 also.
Thank you.
Regards,
Moti
Hello,
The macro below creates combinations of 5 numbers out of 1st 4 groups. (Group1, Group 2, Group 3 and Group 4…are shown in yellow)
But now I require modifications so it can create all possible combinations of 5 numbers using 6 groups.... From Group1 to Group6 (including 2 blue groups 5 and 6 also)
Please see the attached example below….
I tried to change (arrData = Range("A2:D4").Value ' Change here) To (arrData = Range("A2:F4").Value ' Change here) but seems it is design to create combinations only from 4 groups.
Please need help to modify the existing VBA; which cam work with present request. I want which can work with Excel version 2000 also.
VBA Code:
Public Sub Five_numbers()
Range("G1:K50000").ClearContents
Dim dic As Object
Dim arrData, arrDec
Dim isUni As Boolean
Dim arr, arrB
Dim ID As String
Dim i1&, i2&, i3&, i4&, j&, c&, u&, k&
Set dic = CreateObject("Scripting.Dictionary")
ReDim arrDec(1 To 10000, 1 To 5)
ReDim arr(1 To 5)
arrData = Range("A2:D4").Value ' Change here
u = UBound(arrData, 1)
For i1 = 1 To u
arr(1) = arrData(i1, 1)
For i2 = 1 To u
arr(2) = arrData(i2, 2)
For i3 = 1 To u
arr(3) = arrData(i3, 3)
For i4 = 1 To u
arr(4) = arrData(i4, 4)
For c = 1 To 4
For j = 1 To u
isUni = False
Select Case c
Case 1
If j = i1 Then isUni = True
Case 2
If j = i2 Then isUni = True
Case 3
If j = i3 Then isUni = True
Case 4
If j = i4 Then isUni = True
End Select
If isUni = False Then
arr(5) = arrData(j, c)
arrB = Mysort(arr)
ID = arrB(1) & "|" & arrB(2) & "|" & arrB(3) & "|" & arrB(4) & "|" & arrB(5)
If dic.exists(ID) = False Then
k = k + 1
dic.Item(ID) = k
arrDec(k, 1) = arrB(1)
arrDec(k, 2) = arrB(2)
arrDec(k, 3) = arrB(3)
arrDec(k, 4) = arrB(4)
arrDec(k, 5) = arrB(5)
End If
End If
Next j
Next c
Next i4
Next i3
Next i2
Next i1
Range("G1").Resize(k, 5).Value = arrDec
MsgBox k
End Sub
Function Mysort(ByVal a As Variant) As Variant
Dim i As Long, ii As Long, s As Variant
For i = 1 To 4
For ii = i + 1 To 5
If a(i) > a(ii) Then
s = a(i)
a(i) = a(ii)
a(ii) = s
End If
Next ii
Next i
Mysort = a
End Function
MrExcel Question.xlsm | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | Group 1 | Group 2 | Group 3 | Group 4 | Group 5 | Group 6 | G11 | G12 | G21 | G31 | G41 | ||
2 | G11 | G21 | G31 | G41 | G61 | G71 | G11 | G13 | G21 | G31 | G41 | ||
3 | G12 | G22 | G32 | G42 | G62 | G72 | G11 | G21 | G22 | G31 | G41 | ||
4 | G13 | G23 | G33 | G43 | G63 | G73 | G11 | G21 | G23 | G31 | G41 | ||
5 | G11 | G21 | G31 | G32 | G41 | ||||||||
6 | G11 | G21 | G31 | G33 | G41 | ||||||||
7 | G11 | G21 | G31 | G41 | G42 | ||||||||
8 | G11 | G21 | G31 | G41 | G43 | ||||||||
9 | G11 | G12 | G21 | G31 | G42 | ||||||||
10 | G11 | G13 | G21 | G31 | G42 | ||||||||
11 | G11 | G21 | G22 | G31 | G42 | ||||||||
12 | G11 | G21 | G23 | G31 | G42 | ||||||||
13 | G11 | G21 | G31 | G32 | G42 | ||||||||
14 | G11 | G21 | G31 | G33 | G42 | ||||||||
15 | G11 | G21 | G31 | G42 | G43 | ||||||||
16 | G11 | G12 | G21 | G31 | G43 | ||||||||
17 | G11 | G13 | G21 | G31 | G43 | ||||||||
18 | G11 | G21 | G22 | G31 | G43 | ||||||||
19 | G11 | G21 | G23 | G31 | G43 | ||||||||
20 | G11 | G21 | G31 | G32 | G43 | ||||||||
21 | G11 | G21 | G31 | G33 | G43 | ||||||||
22 | G11 | G12 | G21 | G32 | G41 | ||||||||
23 | G11 | G13 | G21 | G32 | G41 | ||||||||
24 | G11 | G21 | G22 | G32 | G41 | ||||||||
25 | G11 | G21 | G23 | G32 | G41 | ||||||||
26 | G11 | G21 | G32 | G33 | G41 | ||||||||
27 | G11 | G21 | G32 | G41 | G42 | ||||||||
28 | G11 | G21 | G32 | G41 | G43 | ||||||||
29 | G11 | G12 | G21 | G32 | G42 | ||||||||
30 | G11 | G13 | G21 | G32 | G42 | ||||||||
31 | G11 | G21 | G22 | G32 | G42 | ||||||||
32 | G11 | G21 | G23 | G32 | G42 | ||||||||
33 | G11 | G21 | G32 | G33 | G42 | ||||||||
34 | G11 | G21 | G32 | G42 | G43 | ||||||||
35 | G11 | G12 | G21 | G32 | G43 | ||||||||
Sheet10 |
Thank you.
Regards,
Moti