Looping Problem

[Paul Black]

Hi Everyone,

I will be Completely Honest Up Front, this is a Lotto Problem, But has
NOTHING to do with Prediction Or Such Like.
If we were to Use 6 Numbers Drawn from 24 Numbers for Example, the FULL
Wheel would Consist of 134,596 Combinations, Achieved Using the Excel
Formula COMBIN(24,6).
For Example, if I was to Use the Following Abbreviated Wheel ( in Cells
"G13:L27" ) of 15 Combinations, it Guarantees that if I have 4 of the 6
Numbers Drawn Within my 24 Numbers, I will have a Minimum Match of 2
Numbers in at Least 1 of my Combinations.

01 03 07 12 15 16
01 04 05 17 20 21
01 08 09 10 19 22
01 13 14 18 23 24
02 03 06 09 21 23
02 10 12 14 16 20
02 11 15 19 20 24
03 04 07 10 18 24
03 05 07 14 17 19
04 06 08 14 15 22
04 09 11 13 16 19
05 10 13 15 17 23
05 11 12 18 21 22
06 08 12 16 17 24
07 08 13 20 22 23

How can I Produce the Total Combinations "Covered" by the Abbreviated
Wheel for Each of the "Matched" Categories Against that of the FULL
Wheel Please.
So the Results ( I Don't Know if the "Covered" Results are Accurate )
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

The Formulas for Tested are ...

Matched Tested Formula
2 if 2 COMBIN(24,2) = 276
2 if 3 COMBIN(24,3) = 2,024
2 if 4 COMBIN(24,4) = 10,626
2 if 5 COMBIN(24,5) = 42,504
2 if 6 COMBIN(24,6) = 134,596
3 if 3 COMBIN(24,3) = 2,024
3 if 4 COMBIN(24,4) = 10,626
3 if 5 COMBIN(24,5) = 42,504
3 if 6 COMBIN(24,6) = 134,596
4 if 4 COMBIN(24,4) = 10,626
4 if 5 COMBIN(24,5) = 42,504
4 if 6 COMBIN(24,6) = 134,596
5 if 5 COMBIN(24,5) = 42,504
5 if 6 COMBIN(24,6) = 134,596

.... if that Helps.
I think to Achieve the Total Combinations "Covered" by the Abbreviated
Wheel is to Probably Loop through ALL the Categories of Possible
Combinations of 6 Numbers from 24 Numbers ( 134,596 Combinations ) and
Keep a Count of the "Covered" Abbreviated Wheel Combinations Against
the Matched FULL Wheel Combinations. Unfortunately I have NO Idea how
to Approach this.
The Abbreviated Wheel Combinations can be Less Or More than they are
Currently ( 15 Combinations ). The Code will Need to Recognise when it
is the Last Combination to Check.
I Hope I have Explained this Clearly Enough.

Any Help will be Greatly Appreciated.
Many Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Everyone,

I will Try and Explain this a Bit Clearer. I think in my Previous Post
was to Involved and Difficult to Understand.
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) ] 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 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 ( 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 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 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.
PAB

 

[Paul Black]

Hi Everyone,

I will Try and Explain this a Bit Clearer. I think in my Previous Post
was to Involved and Difficult to Understand.
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) ] 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 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 ( 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 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 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