P
Paul Black
Hello Everybody,
I am Basically trying to Find Out the Combinations Covered in a List of
Combinations.
I have a Set of 6 Number Combinations in Cells "G13:L27" ( the Number
of Combinations could be More or Less ).
In this Example I am Using a Wheel with 24 Numbers :-
1,3,7,12,15,16
1,4,5,17,20,21
1,8,9,10,19,22
1,13,14,18,23,24
2,3,6,9,21,23
2,10,12,14,16,20
2,11,15,19,20,24
3,4,7,10,18,24
3,5,7,14,17,19
4,6,8,14,15,22
4,9,11,13,16,19
5,10,13,15,17,23
5,11,12,18,21,22
6,8,12,16,17,24
7,8,13,20,22,23
Here is the Code Somebody Kindly Provided which Cycles through ALL
Combinations ( 6 Numbers from 24 Numbers in this Case Produces 134,596
Combinations ) and Compares ALL the Combinations with ALL the
Combinations in the Above Wheel. The Below Code Finds the Coverage of 5
Numbers if 5 Numbers Matched ...
Sub test_5()
Dim a, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
a = Range("g13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub
.... and Produces the Correct Result of 90.
How can the Code be Modified ( Or Improved ) to Also Produce the
Combinations Covered for the Categories ...
Matched Covered
2 if 5 = 42,504
3 if 5 = 35,720
4 if 5 = 4,140
5 if 5 = 90 ( the Code Already Provides this Result )
.... Please.
I Hope I have Explained this Clear Enough.
Many Thanks in Advance.
All the Best.
Paul
I am Basically trying to Find Out the Combinations Covered in a List of
Combinations.
I have a Set of 6 Number Combinations in Cells "G13:L27" ( the Number
of Combinations could be More or Less ).
In this Example I am Using a Wheel with 24 Numbers :-
1,3,7,12,15,16
1,4,5,17,20,21
1,8,9,10,19,22
1,13,14,18,23,24
2,3,6,9,21,23
2,10,12,14,16,20
2,11,15,19,20,24
3,4,7,10,18,24
3,5,7,14,17,19
4,6,8,14,15,22
4,9,11,13,16,19
5,10,13,15,17,23
5,11,12,18,21,22
6,8,12,16,17,24
7,8,13,20,22,23
Here is the Code Somebody Kindly Provided which Cycles through ALL
Combinations ( 6 Numbers from 24 Numbers in this Case Produces 134,596
Combinations ) and Compares ALL the Combinations with ALL the
Combinations in the Above Wheel. The Below Code Finds the Coverage of 5
Numbers if 5 Numbers Matched ...
Sub test_5()
Dim a, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
a = Range("g13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub
.... and Produces the Correct Result of 90.
How can the Code be Modified ( Or Improved ) to Also Produce the
Combinations Covered for the Categories ...
Matched Covered
2 if 5 = 42,504
3 if 5 = 35,720
4 if 5 = 4,140
5 if 5 = 90 ( the Code Already Provides this Result )
.... Please.
I Hope I have Explained this Clear Enough.
Many Thanks in Advance.
All the Best.
Paul