|
Looping Problem
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 |
Looping Problem
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 wrote: 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 |
Looping Problem
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 |
Looping Problem
Hi Everyone,
Is it Feasible that the Code I Posted could be Adapted to Produce the Required Results, Or is its Structure and Logic Totally Different Please. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
| All times are GMT +1. The time now is 12:02 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com