P
Paul Black
Hi Everyone,
I have Adapted some Code Originally Written by Tom Ogilvy for Something
Else ( Thanks Very Much Tom ), which May be of Help for this Request.
Option Explicit
Sub Covered()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim DrawnFrom As Integer
Dim icnt As Integer
Dim lngCount(0 To 6) As Long
Dim lngSum As Long
Dim nCount As Long
Dim s As Integer
Dim varray As Variant
varray = Array(1, 2, 3, 4, 5, 6)
Application.ScreenUpdating = False
DrawnFrom = ActiveSheet.Range("A1")
For A = 1 To DrawnFrom - 5
For B = A + 1 To DrawnFrom - 4
For C = B + 1 To DrawnFrom - 3
For D = C + 1 To DrawnFrom - 2
For E = D + 1 To DrawnFrom - 1
For F = E + 1 To DrawnFrom
nCount = nCount + 1
If True Then
icnt = 0
For s = 0 To 5
If A = varray(s) Then icnt = icnt + 1
If B = varray(s) Then icnt = icnt + 1
If C = varray(s) Then icnt = icnt + 1
If D = varray(s) Then icnt = icnt + 1
If E = varray(s) Then icnt = icnt + 1
If F = varray(s) Then icnt = icnt + 1
Next
lngCount(icnt) = lngCount(icnt) + 1
End If
Next
Next
Next
Next
Next
Next
lngSum = 0
Range("N2").Select
For s = 0 To 6
If s >= 3 Then lngSum = lngSum + lngCount(s)
ActiveCell.Value = s & " Matches = " & Format(lngCount(s), "#,###")
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
I will Try and Explain this as Clear as Possible.
I have a 24 Number Abbreviated Wheel ( in Cells "G13:L27" ) of 15
Combinations with 6 Numbers in EACH Combination ( Other Wheels to Check
could have Less Or More Combinations than Currently so the Code will
Need to Recognise when it is the Last Combination to Check ).
Therefore, for this Example, there are 134,596 Combinations [ Excel
Formula COMBIN(24,6) ] to Check Against the 15 Combinations in the
Abbreviated Wheel in Total.
The Basis of what I am Trying to Achieve is to take the Wheel, Generate
ALL 134,596 Combinations for the Total Selected Numbers in the Wheel (
24 in this Example ) and Match ( Compare ) EACH Combination with the
Lines in the Wheel. Then Collate ALL the Results as Per the Categories
Below.
So the Total Combinations "Covered" for the Matched Category 2 if 3 for
Example, Means the Total Combinations "Covered" in the Abbreviated
Wheel ( for ALL 15 Combinations ) that have 2 Matched Numbers if I have
3 Matched Numbers of the 6 Numbers Drawn Within my Selection.
So the Results ( I Don't Know if the "Covered" Results Below are
Accurate ) for EACH Category would Look something like this :-
Matched Tested Covered
2 if 2 276 209
2 if 3 2,024 2,008
2 if 4 10,626 10,626
2 if 5 42,504 42,504
2 if 6 134,596 134,596
3 if 3 2,024 300
3 if 4 10,626 5,289
3 if 5 42,504 35,720
3 if 6 134,596 131,922
4 if 4 10,626 225
4 if 5 42,504 4,140
4 if 6 134,596 35,304
5 if 5 42,504 90
5 if 6 134,596 1,635
I can see the Logic of what Needs to be Done, But Unfortunately I have
NO Idea how to Continue and Achieve the Required Results.
Any Help will be Greatly Appreciated.
Many Thanks in Advance.
All the Best.
Paul
I have Adapted some Code Originally Written by Tom Ogilvy for Something
Else ( Thanks Very Much Tom ), which May be of Help for this Request.
Option Explicit
Sub Covered()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim DrawnFrom As Integer
Dim icnt As Integer
Dim lngCount(0 To 6) As Long
Dim lngSum As Long
Dim nCount As Long
Dim s As Integer
Dim varray As Variant
varray = Array(1, 2, 3, 4, 5, 6)
Application.ScreenUpdating = False
DrawnFrom = ActiveSheet.Range("A1")
For A = 1 To DrawnFrom - 5
For B = A + 1 To DrawnFrom - 4
For C = B + 1 To DrawnFrom - 3
For D = C + 1 To DrawnFrom - 2
For E = D + 1 To DrawnFrom - 1
For F = E + 1 To DrawnFrom
nCount = nCount + 1
If True Then
icnt = 0
For s = 0 To 5
If A = varray(s) Then icnt = icnt + 1
If B = varray(s) Then icnt = icnt + 1
If C = varray(s) Then icnt = icnt + 1
If D = varray(s) Then icnt = icnt + 1
If E = varray(s) Then icnt = icnt + 1
If F = varray(s) Then icnt = icnt + 1
Next
lngCount(icnt) = lngCount(icnt) + 1
End If
Next
Next
Next
Next
Next
Next
lngSum = 0
Range("N2").Select
For s = 0 To 6
If s >= 3 Then lngSum = lngSum + lngCount(s)
ActiveCell.Value = s & " Matches = " & Format(lngCount(s), "#,###")
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub
I will Try and Explain this as Clear as Possible.
I have a 24 Number Abbreviated Wheel ( in Cells "G13:L27" ) of 15
Combinations with 6 Numbers in EACH Combination ( Other Wheels to Check
could have Less Or More Combinations than Currently so the Code will
Need to Recognise when it is the Last Combination to Check ).
Therefore, for this Example, there are 134,596 Combinations [ Excel
Formula COMBIN(24,6) ] to Check Against the 15 Combinations in the
Abbreviated Wheel in Total.
The Basis of what I am Trying to Achieve is to take the Wheel, Generate
ALL 134,596 Combinations for the Total Selected Numbers in the Wheel (
24 in this Example ) and Match ( Compare ) EACH Combination with the
Lines in the Wheel. Then Collate ALL the Results as Per the Categories
Below.
So the Total Combinations "Covered" for the Matched Category 2 if 3 for
Example, Means the Total Combinations "Covered" in the Abbreviated
Wheel ( for ALL 15 Combinations ) that have 2 Matched Numbers if I have
3 Matched Numbers of the 6 Numbers Drawn Within my Selection.
So the Results ( I Don't Know if the "Covered" Results Below are
Accurate ) for EACH Category would Look something like this :-
Matched Tested Covered
2 if 2 276 209
2 if 3 2,024 2,008
2 if 4 10,626 10,626
2 if 5 42,504 42,504
2 if 6 134,596 134,596
3 if 3 2,024 300
3 if 4 10,626 5,289
3 if 5 42,504 35,720
3 if 6 134,596 131,922
4 if 4 10,626 225
4 if 5 42,504 4,140
4 if 6 134,596 35,304
5 if 5 42,504 90
5 if 6 134,596 1,635
I can see the Logic of what Needs to be Done, But Unfortunately I have
NO Idea how to Continue and Achieve the Required Results.
Any Help will be Greatly Appreciated.
Many Thanks in Advance.
All the Best.
Paul