Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Everyone,
This is Lotto Based. There is a System in Lotto called "Wheels". A Lotto Wheeling System is a Special Pattern to Arrange Numbers into Combinations. This System can be Used Over and Over again with Different Numbers. A Lotto Wheel is Constructed in such a way that if the Winning Numbers Fall in the Group of Numbers you have Selected, you will Always have a Winning Combination Somewhere. If for Example, you have a Group of 12 Numbers then the Lotto Wheel you Select should have the Numbers 1-12 Arranged in Sets of Numbers ( Each Set is 6 Numbers ). You then take the Wheel and Substitute your Numbers in the Wheels Pattern and Simply Replace all the 1's in the Pattern with your First Number, all the 2's with your Second Number, all the 3's with your Third Number, etc. All Wheels give a Guarantee. For Example, the Wheel 24,6,3,6,50 Means, there are 24 Different Numbers Used in the Wheel, there are 6 Numbers Drawn, the Guarantee of having 3 Numbers in at Least 1 Combination if ALL the 6 Numbers Drawn are in the Selected 24 Numbers. 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 that someone has Kindly Provided which Cycles through ALL Combinations and Compares ALL the Combinations with ALL the Combinations in the Above Wheel. The Below Code Finds the Coverage ( Total Combinations ) of 5 Numbers if 5 Numbers are Matched … Code: 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 to Also Produce the Combinations Covered for the Categories … Matched = Covered Combinations 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 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 I have Explained this Clear Enough. Many Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Everyone,
I think I have probably used the word subset in error. I am basically just trying to cycle through the combinations already in the Wheel and find out the coverage ( total combinations for each category ). Any help will be greatly appreciated. Thanks in advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Since you don't provide a background on this discussion, I cannot be certain
the foll. will be helpful but hopefully it will be. Powerset, Subset, and Combinations & Permutations http://www.tushar-mehta.com/excel/tips/powerset.html -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Everyone, I think I have probably used the word subset in error. I am basically just trying to cycle through the combinations already in the Wheel and find out the coverage ( total combinations for each category ). Any help will be greatly appreciated. Thanks in advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Thanks for the reply Tushar Mehta,
I will try to Explain it a bit Clearer. Lets take our First 6 Number Combination in the Wheel of 1,3,7,12,15,16. Now if we take the First 5 Numbers 1,3,7,12,15. There is 1 Combination of 5 Numbers from 5, it is as Follows :- Combination 1 = 1,3,7,12,15 There are 5 Combinations of 4 Numbers from 5, they are as Follows :- Combination 1 = 1,3,7,12 Combination 2 = 1,3,7,15 Combination 3 = 1,3,12,15 Combination 4 = 1,7,12,15 Combination 5 = 3,7,12,15 There are 10 Combinations of 3 Numbers from 5, they are as Follows :- Combination 1 = 1,3,7 Combination 2 = 1,3,12 Combination 3 = 1,3,15 Combination 4 = 1,7,12 Combination 5 = 1,7,15 Combination 6 = 1,12,15 Combination 7 = 3,7,12 Combination 8 = 3,7,15 Combination 9 = 3,12,15 Combination 10 = 7,12,15 There are 10 Combinations of 2 Numbers from 5, they are as Follows :- Combination 1 = 1,3 Combination 2 = 1,7 Combination 3 = 1,12 Combination 4 = 1,15 Combination 5 = 3,7 Combination 6 = 3,12 Combination 7 = 3,15 Combination 8 = 7,12 Combination 9 = 7,15 Combination 10 = 12,15 Now this Needs to be Repeated with the Remaining 5 Combinations of 5 Numbers from 6 in Our First Combination in the Wheel :- Combination 1 = 1,3,7,12,15 ( Already Produced Above ) Combination 2 = 1,3,7,12,16 Combination 3 = 1,3,7,15,16 Combination 4 = 1,3,12,15,16 Combination 5 = 1,7,12,15,16 Combination 6 = 3,7,12,15,16 Then the Whole of the Above Needs to be Repeated for the Remaining 14 Combinations in this Particular Wheel ( Other Wheels Might be Smaller or Larger ). Obviously if a Combination for a Certain Category has Already been Counted ( from ANY Other Combination in the Wheel ) then the Repeated Combination is NOT to be Added to the Total Combinations for that Particular Category. I Hope this Make it a Bit Easier to Understand. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Thanks for the Reply,
I will Certainly have a Look at Generating the Power Set, although Adapting it will be Beyond my Capabilities. Do you think that this will do as I Require. Thanks VERY Much again. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Tushar Mehta,
I have had a Look at the Code for Generating the Power Set and I think it Might Accomodate my Needs. The Only Problem is that I do NOT have Enough VBA Skills to be Able to Adapt it to Produce my Required Results. Any Help will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Why not generate the powerset and then delete all the oneses by hand? It
will take all of a few seconds! -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar Mehta, I have had a Look at the Code for Generating the Power Set and I think it Might Accomodate my Needs. The Only Problem is that I do NOT have Enough VBA Skills to be Able to Adapt it to Produce my Required Results. Any Help will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Tushar Mehta,
I am still at a loss on how I can use the PowerSet code to find the information I am looking for if my Data is in Cells "G13:L27". Can you please advise me on how I can achieve the required results. Thanks in advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Tushar Mehta,
Any Help will be greatly appreciated. Thanks in Advance. All the Best. Paul Paul Black wrote: Hi Tushar Mehta, I am still at a loss on how I can use the PowerSet code to find the information I am looking for if my Data is in Cells "G13:L27". Can you please advise me on how I can achieve the required results. Thanks in advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculate the Number of Subsets
Hi Tushar Mehta,
I am still at a loss on how I can use the PowerSet code to find the information I am looking for if my Data is in Cells "G13:L27". Can you please advise me on how I can achieve the required results. Thanks in advance. All the Best. Paul Tushar Mehta wrote: Why not generate the powerset and then delete all the oneses by hand? It will take all of a few seconds! -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article , says... Hi Tushar Mehta, I have had a Look at the Code for Generating the Power Set and I think it Might Accomodate my Needs. The Only Problem is that I do NOT have Enough VBA Skills to be Able to Adapt it to Produce my Required Results. Any Help will be GREATLY Appreciated. Thanks in Advance. All the Best. Paul *** Sent via Developersdex http://www.developersdex.com *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating subsets | Excel Discussion (Misc queries) | |||
Manipulating subsets of large datasets | Excel Worksheet Functions | |||
Xcl-create subsets of a set of numbers w/o duplicating the subset | Excel Worksheet Functions | |||
generate all subsets (2^n array) | Excel Programming | |||
Getting subsets of data | Excel Programming |