P
Paul Black
Hi Everyone,
I have been Trying to Achieve this for Several Months.
The Code below Cycles through a Range of 6 Number Combinations ( 15
Combinations in Cells G13:L27 ) and Produces the Total 5 Number
Combinations Covered ( 90, which is Correct ) out of the 42,504 (
=COMBIN(24,5) ) Combinations that are Available with the Maximum Number
being Used in the Wheel, 24 in this Case ( the Maximum Number could be
Lower or Higher ).
How can the Code below be Adapted to ALSO Calculate ( Value to go in
Cell O17 ) how Many Combinations are Covered for 3 if 5 Please.
To Calculate the 3 if 5 Category you Need to Cycle through ALL 5 Number
Combinations that can be Constructed from the Total Numbers Used in the
Wheel ( 24 in this Case ). So if the Wheel Contains "x" Unique Numbers,
you Need to Cycle through ALL 5 Number Combinations from those "x"
Numbers. Then you Need to Scan the Wheel for Each 5 Number Combination
Produced and Compare it with Each Line in the Wheel to see if that Line
Matches the 5 Number Combination in *EXACTLY* 3 Numbers. If it does,
then that Combination of 3 if 5 is Covered and Added to the Total and
there is NO Need to Continue to Check for that Particular Combination
Any Further. You then go onto the Next Combination to Check and so on
Until ALL Combinations have been Cycled through and Checked with the
Wheel.
Basically, although there are 6 Numbers for Each Combination, I Need to
Cycle through Each 5 Number Combination that can be made from the
Maximum Number being Used in the Wheel, 24 in this Case ( 42,504
(=COMBIN(24,5) Available in Total ), and Count how Many of those are
Covered in the Wheel ( 90 in this Case ). Then of those 90 Combinations
Covered, I would like to Count how Many 3 Number Combinations ( 2,024
(=COMBIN(24,3) Available in Total ) are Covered in those Ninety 5
Number Combinations.
Code:
Sub test_for_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
I would also like to Calculate the 4 if 5 & 2 if 5 Categories if
Possible Please.
I Hope I have Explained this Clearly Enough.
Thanks Very Much in Advance.
All the Best.
Paul
I have been Trying to Achieve this for Several Months.
The Code below Cycles through a Range of 6 Number Combinations ( 15
Combinations in Cells G13:L27 ) and Produces the Total 5 Number
Combinations Covered ( 90, which is Correct ) out of the 42,504 (
=COMBIN(24,5) ) Combinations that are Available with the Maximum Number
being Used in the Wheel, 24 in this Case ( the Maximum Number could be
Lower or Higher ).
How can the Code below be Adapted to ALSO Calculate ( Value to go in
Cell O17 ) how Many Combinations are Covered for 3 if 5 Please.
To Calculate the 3 if 5 Category you Need to Cycle through ALL 5 Number
Combinations that can be Constructed from the Total Numbers Used in the
Wheel ( 24 in this Case ). So if the Wheel Contains "x" Unique Numbers,
you Need to Cycle through ALL 5 Number Combinations from those "x"
Numbers. Then you Need to Scan the Wheel for Each 5 Number Combination
Produced and Compare it with Each Line in the Wheel to see if that Line
Matches the 5 Number Combination in *EXACTLY* 3 Numbers. If it does,
then that Combination of 3 if 5 is Covered and Added to the Total and
there is NO Need to Continue to Check for that Particular Combination
Any Further. You then go onto the Next Combination to Check and so on
Until ALL Combinations have been Cycled through and Checked with the
Wheel.
Basically, although there are 6 Numbers for Each Combination, I Need to
Cycle through Each 5 Number Combination that can be made from the
Maximum Number being Used in the Wheel, 24 in this Case ( 42,504
(=COMBIN(24,5) Available in Total ), and Count how Many of those are
Covered in the Wheel ( 90 in this Case ). Then of those 90 Combinations
Covered, I would like to Count how Many 3 Number Combinations ( 2,024
(=COMBIN(24,3) Available in Total ) are Covered in those Ninety 5
Number Combinations.
Code:
Sub test_for_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
I would also like to Calculate the 4 if 5 & 2 if 5 Categories if
Possible Please.
I Hope I have Explained this Clearly Enough.
Thanks Very Much in Advance.
All the Best.
Paul