|
If ... Then Loop
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 |
If ... Then Loop
I Don't know if this Helps with the Overall Explanation or Not.
I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
was Told . . .
Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
Hi Tom,
You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
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 ... The code you show doesn't do what you say. It generates 90 numbers (the 6 combinations per row) and counts those that are unique. It only loops 90 times. There is no comparison to 134,596 numbers, nor does it generate those numbers. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
Thanks for the Reply and Information Tom.
Is there any way that the Code can be adapted to accomodate the criteria I specified at all Please. Any help will be GREATLY appreciated. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: 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 ... The code you show doesn't do what you say. It generates 90 numbers (the 6 combinations per row) and counts those that are unique. It only loops 90 times. There is no comparison to 134,596 numbers, nor does it generate those numbers. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
Hi Again,
I have Tried Adapting the Code but to NO Avail. I keep getting the ERROR Message ... This Key is Already Associated with an Element of this Collection. .... when I Compile & Run it. Can the Code be Written Without Using ... Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") .... as this is the Problem I think. To be Honest, I have NO Idea what this Piece of Code Actually does, but it Seems to Stop Any Additional Requirements from being Executed. I have been trying to get an Answer to this for about Six Months. Any Help or Suggestions will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul Paul Black wrote: Thanks for the Reply and Information Tom. Is there any way that the Code can be adapted to accomodate the criteria I specified at all Please. Any help will be GREATLY appreciated. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: 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 ... The code you show doesn't do what you say. It generates 90 numbers (the 6 combinations per row) and counts those that are unique. It only loops 90 times. There is no comparison to 134,596 numbers, nor does it generate those numbers. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
Hi Everyone,
I will try to Explain it a bit Clearer. Lets Assume we have a Six Number Combination of 1,2,3,4,5,6. There are 6 Combinations of 5 Numbers from 6, they are as Follows :- Combination 1 = 1,2,3,4,5 Combination 2 = 1,2,3,4,6 Combination 3 = 1,2,3,5,6 Combination 4 = 1,2,4,5,6 Combination 5 = 1,3,4,5,6 Combination 6 = 2,3,4,5,6 Now if we just Concentrate on the Fact that we are Lucky Enough to have 5 of the 6 Numbers Drawn, Numbers 1,2,3,4,5 for Example. There are 5 Combinations of 4 Numbers from 5, they are as Follows :- Combination 1 = 1,2,3,4 Combination 2 = 1,2,3,5 Combination 3 = 1,2,4,5 Combination 4 = 1,3,4,5 Combination 5 = 2,3,4,5 There are 10 Combinations of 3 Numbers from 5, they are as Follows :- Combination 1 = 1,2,3 Combination 2 = 1,2,4 Combination 3 = 1,2,5 Combination 4 = 1,3,4 Combination 5 = 1,3,5 Combination 6 = 1,4,5 Combination 7 = 2,3,4 Combination 8 = 2,3,5 Combination 9 = 2,4,5 Combination 10 = 3,4,5 There are 10 Combinations of 2 Numbers from 5, they are as Follows :- Combination 1 = 1,2 Combination 2 = 1,3 Combination 3 = 1,4 Combination 4 = 1,5 Combination 5 = 2,3 Combination 6 = 2,4 Combination 7 = 2,5 Combination 8 = 3,4 Combination 9 = 3,5 Combination 10 = 4,5 As I said Previously :- I was Told for the Interpretation of the 3 if 5 Category that 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. I Hope this makes it a Bit Clearer what I am Trying to Achieve. I Appreciate your Time & Effort with this. All the Best. Paul Paul Black wrote: Hi Again, I have Tried Adapting the Code but to NO Avail. I keep getting the ERROR Message ... This Key is Already Associated with an Element of this Collection. ... when I Compile & Run it. Can the Code be Written Without Using ... Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") ... as this is the Problem I think. To be Honest, I have NO Idea what this Piece of Code Actually does, but it Seems to Stop Any Additional Requirements from being Executed. I have been trying to get an Answer to this for about Six Months. Any Help or Suggestions will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul Paul Black wrote: Thanks for the Reply and Information Tom. Is there any way that the Code can be adapted to accomodate the criteria I specified at all Please. Any help will be GREATLY appreciated. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: 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 ... The code you show doesn't do what you say. It generates 90 numbers (the 6 combinations per row) and counts those that are unique. It only loops 90 times. There is no comparison to 134,596 numbers, nor does it generate those numbers. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
If ... Then Loop
I will Try and Explain this Clearly One Last Time.
This is what I have been told. The maths is straightforward to do this. We have a wheel C(n,k,t,m)=b where ... *n=the total balls we want to wheel *k=the ticket size (e.g. a 6 ball game has k=6) *t=the prize division we want to guarantee a win *m=the condition that has to be met, in order to guarantee the t prize division win; m defines the least number of balls from our n set that must be correct. *b=the total tickets required to play. Now, if we are interested to find the coverage achieved in a certain category e.g. x if y, then the total combinations that need to be covered are nCk(n,y)=A. Thus, you have to test A combinations, each one containing y numbers against the tickets of your wheel (each ticket contains k numbers). A combination of those A is covered if there is at least one ticket in your wheel, that contains at least x numbers in common. All you have to do is to go through all A combinations and test each of them if it contains at least x numbers in common with at least one ticket of your wheel. If it does, then it is covered. You produce all combinations (not numbers) for the x if y category and test each such combination (contains y numbers) if it is covered by at least one combination in your wheel (k numbers). You don't test against all numbers in the wheel. A combination of "x if y" is covered if there is at least one ticket in your wheel that has in common t numbers. e.g. for a simple wheel C(5,4,3,3)=4 ... 1) 1 2 3 4 2) 1 2 3 5 3) 1 2 4 5 4) 1 3 4 5 .... we want to test the 2 if 3 (to be covered), nCk(5,3)=10 combinations ... 1 2 3 1 2 4 1 2 5 1 3 4 1 3 5 1 4 5 2 3 4 2 3 5 2 4 5 3 4 5 We go through all 10 combinations to find the coverage. E.g. we test 1 2 3 with our wheel. We can see that 1) & 2) & 3) & 4) tickets cover this combination as they contain at least t=2 numbers in common, thus 1 2 3 is covered. We need only one ticket in the wheel to cover our combination. If you test the above 10 combinations, all of them are covered, thus the C(5,4,3,3)=4 has 2if3=100% coverage. The algorithm in plain English (create array, iterate this cycle, if-else etc) code:-------------------------------------------------------------------------------- declare partial_results as structure of min = array of numbers[ 4 ]; //min[0] is match in six numbers, min[1] is match in five numbers, etc max = array of numbers[ 4 ]; comboCount as number; end declare function main declare final_results as list; for each c=1,2,3,4,5,6 to 44,45,46,47,48,49 do results = new partial_results; for each d=(tickets being tested) do match = how_many_numbers_match( c, d ); increment( results, match ); end for similar = find_similar_results( final_results, results ); if similar is NULL then add results to final_results; else add( results, similar ); end if end for //now the final_results list contains all data for the coverage report //the following applies to each item in this list: //"comboCount tested combinations produce min[0] to max[0] of jackpot hits, //and min[1] to max[1] of 'match 5' hits and min[2] to max[2] of 'match 4' hits //and min[3] to max[3] of 'match 3' hits end function function increment( res as partial_results, match as number ) index_to_increment = 4 - (match-2); //reverse the match index to have highest match at index '0' min[ index_to_increment ] = min[ index_to_increment ] + 1; max[ index_to_increment ] = max[ index_to_increment ] + 1; comboCount = comboCount+1; end function function add( res1 as partial_results, res2 as partial_results ) res2.comboCount = res2.comboCount + res1.comboCount; for i=0 to 3 do if res1.min[ i ] < res2.min[ i ] then res2.min[ i ] = res1.min[ i ]; end if if res1.max[ i ] res2.max[ i ] then res2.max[ i ] = res1.max[ i ]; end if end for end function function find_similar_results( list, results ) for each r=partial_results from the list do for i=0 to 3 do if results.max[ i ] 0 then if results.max[i] = r.max[ i ] then return r; else break; end if else if r.max[ i ] 0 then break; end if end for end for return NULL; end function The algorithm calculates the coverage on assumption that you hit 6 from your wheel. if you want to see coverage report for hit 5 or hit 4 only then the outermost loop must be shortened to ... for each c=1,2,3,4,5 to 45,46,47,48,49 do or for each c=1,2,3,4 to 46,47,48,49 do .... respectively. Any Help will be Appreciated. All the Best. Paul Paul Black wrote: Hi Everyone, I will try to Explain it a bit Clearer. Lets Assume we have a Six Number Combination of 1,2,3,4,5,6. There are 6 Combinations of 5 Numbers from 6, they are as Follows :- Combination 1 = 1,2,3,4,5 Combination 2 = 1,2,3,4,6 Combination 3 = 1,2,3,5,6 Combination 4 = 1,2,4,5,6 Combination 5 = 1,3,4,5,6 Combination 6 = 2,3,4,5,6 Now if we just Concentrate on the Fact that we are Lucky Enough to have 5 of the 6 Numbers Drawn, Numbers 1,2,3,4,5 for Example. There are 5 Combinations of 4 Numbers from 5, they are as Follows :- Combination 1 = 1,2,3,4 Combination 2 = 1,2,3,5 Combination 3 = 1,2,4,5 Combination 4 = 1,3,4,5 Combination 5 = 2,3,4,5 There are 10 Combinations of 3 Numbers from 5, they are as Follows :- Combination 1 = 1,2,3 Combination 2 = 1,2,4 Combination 3 = 1,2,5 Combination 4 = 1,3,4 Combination 5 = 1,3,5 Combination 6 = 1,4,5 Combination 7 = 2,3,4 Combination 8 = 2,3,5 Combination 9 = 2,4,5 Combination 10 = 3,4,5 There are 10 Combinations of 2 Numbers from 5, they are as Follows :- Combination 1 = 1,2 Combination 2 = 1,3 Combination 3 = 1,4 Combination 4 = 1,5 Combination 5 = 2,3 Combination 6 = 2,4 Combination 7 = 2,5 Combination 8 = 3,4 Combination 9 = 3,5 Combination 10 = 4,5 As I said Previously :- I was Told for the Interpretation of the 3 if 5 Category that 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. I Hope this makes it a Bit Clearer what I am Trying to Achieve. I Appreciate your Time & Effort with this. All the Best. Paul Paul Black wrote: Hi Again, I have Tried Adapting the Code but to NO Avail. I keep getting the ERROR Message ... This Key is Already Associated with an Element of this Collection. ... when I Compile & Run it. Can the Code be Written Without Using ... Dim dic As Object Set dic = CreateObject("Scripting.Dictionary") ... as this is the Problem I think. To be Honest, I have NO Idea what this Piece of Code Actually does, but it Seems to Stop Any Additional Requirements from being Executed. I have been trying to get an Answer to this for about Six Months. Any Help or Suggestions will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul Paul Black wrote: Thanks for the Reply and Information Tom. Is there any way that the Code can be adapted to accomodate the criteria I specified at all Please. Any help will be GREATLY appreciated. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: 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 ... The code you show doesn't do what you say. It generates 90 numbers (the 6 combinations per row) and counts those that are unique. It only loops 90 times. There is no comparison to 134,596 numbers, nor does it generate those numbers. -- Regards, Tom Ogilvy "Paul Black" wrote in message oups.com... Hi Tom, You are Quite Right, you have Previously Provided me with Excellent Code that Produces the Exact Results that you have Posted. That Code Cycled through ALL Combinations in a 649 Lotto and Produce the List of ALL Possible Winning Combinations in Each Category. This Request is Different Because it is Based on a List of Set Numbers and Involves Different Criteria. Thanks in Advance. All the Best. Paul Tom Ogilvy wrote: was Told . . . Your acting like you have never asked for anthing like this before? Produces: 13983816 0 Matches (no bonus): 6096454 1 Matches (no bonus): 5775588 2 Matches (no bonus): 1851150 3 Matches (no bonus): 246820 4 Matches (no bonus): 13545 5 Matches (no bonus): 252 6 Matches (With bonus): 6 6 Matches (no bonus): 1 At least 3 matches 260624 -- Regards, Tom Ogilvy "Paul Black" wrote in message ups.com... I Don't know if this Helps with the Overall Explanation or Not. I was Told for the Interpretation of the 3 if 5 Category that 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. Thanks in Advance. All the Best. Paul Paul Black wrote: 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 |
| All times are GMT +1. The time now is 05:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com