Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused with lots of loops - Please help
I am trying to analyze Keno numbers. Following is a list of sample
draws. I have listed just 17 draws, it can be more than that. DrawID,Date,N1,N2,N3,N4,N5,N6,N7,N8,N9,N10,N11,N12 ,N13,N14,N15,N16,N17,N18,N19,N20 1,01/01/06,3,4,6,10,11,13,18,21,30,32,33,35,46,53,60,67,69 ,74,77,78 2,01/02/06,3,4,7,9,10,11,21,32,33,35,37,41,47,57,60,64,69, 72,74,75 3,01/03/06,4,7,13,15,17,25,29,32,37,42,45,47,50,57,60,64,6 8,71,72,74 4,01/04/06,4,5,6,9,12,15,19,20,30,34,35,38,45,47,54,56,63, 65,72,78 5,01/05/06,5,6,9,12,15,21,26,31,32,43,44,47,64,66,67,68,69 ,74,75,80 6,01/06/06,6,9,13,16,21,22,31,46,48,49,52,61,63,64,69,70,7 1,75,78,79 7,01/07/06,4,7,9,10,11,20,28,29,30,32,34,35,40,41,49,52,66 ,69,70,74 8,01/08/06,3,4,8,10,14,20,21,23,28,29,32,37,44,47,48,49,56 ,64,69,72 9,01/09/06,1,6,9,10,11,13,21,25,29,33,36,43,48,49,51,52,63 ,65,72,74 10,01/10/06,1,3,7,11,14,18,27,33,35,37,39,41,45,47,48,53,64 ,65,75,77 11,01/11/06,3,4,5,6,11,13,15,18,28,29,35,56,61,63,64,69,71, 74,75,80 12,01/12/06,3,7,10,11,16,18,28,34,35,43,47,51,52,55,56,57,6 0,64,71,72 13,01/13/06,3,13,15,21,24,27,28,35,47,48,49,54,56,57,63,72, 75,76,77,79 14,01/14/06,4,6,9,10,15,21,31,33,34,41,42,45,46,47,57,60,68 ,72,74,78 15,01/15/06,4,6,9,10,12,13,15,21,22,31,35,47,49,52,56,63,64 ,72,74,75 16,01/16/06,8,9,10,12,16,21,22,28,38,47,49,51,52,53,54,55,6 4,66,71,72 17,01/17/06,3,4,7,10,14,17,18,21,28,31,33,36,37,43,47,57,65 ,69,75,80 Problem : I want to list only those combinations that meets a given criteria. ---------------------------------------------------------------------------------- I need a vba that gives three prompts to the user which asks for Combinations, Matches and Frequency. Lets say the user enters: Combinations (C) : 5 Matches (M) : =4 Frequency (F) : =8 Considering the above criteria, the macro should list combinations of 5 numbers (C) out of which any 4 numbers (M) matches in more than or equal to 8 draws (F). For instance, have a look at the following combinations: 10, 21, 28, 47, 72 = Any 4 or more numbers from this combination matched with draw numbers 2,8,12,13,14,15,16,17 10, 21, 47, 57, 72 = Any 4 or more numbers from this combination matched with draw numbers 2,8,12,13,14,15,16,17 15, 21, 47, 64, 72 = Any 4 or more numbers from this combination matched with draw numbers 2,3,5,8,13,14,15,16 21, 47, 57, 64, 72 = Any 4 or more numbers from this combination matched with draw numbers 2,3,8,12,13,14,15,16 3, 10, 21, 47, 72 = Any 4 or more numbers from this combination matched with draw numbers 2,8,12,13,14,15,16,17 I want the combinations listed in the following format C1,C2,C3,C4,C5,Frq,dID1,dID2,dID3,dID4,dID5,dID6,d ID7,dID8 ---------------------------------------------------------- 10,21,28,47,72,8,2,8,12,13,14,15,16,17 10,21,47,57,72,8,2,8,12,13,14,15,16,17 15,21,47,64,72,8,2,3,5,8,13,14,15,16 21,47,57,64,72,8,2,3,8,12,13,14,15,16 3,10,21,47,72,8,2,8,12,13,14,15,16,17 The combination first, then the frequency and their Draw ID numbers (In different cells and not in comma separated values) The logic I tried: ----------------- I thought of making combinations drawwise. If I want to list combinations of 5 numbers, then first combination would be 3,4,6,10,11. Before listing this combination, check if it fulfils the criteria. If yes, then list it else move to the next combinations. Do this for 15504 times [ =COMBIN(20,5) ] to ensure that we have analysed all possible combinations of the first draw. Do the same thing for the next 16 draws. The challenge that I faced is that I can do it if the first parameter Combinations (C) is known. The problem is that the combinations that are being generated are user specific, it can be 2 or as high as 10. This is where I am getting stuck. There are so many loops involved that I am now confused and not getting correct results. I would appreciate if anybody can help me with this. I hope I have explained my problem well. Please let me know if any of you need any clarification. Optional: If possible, I would also want an indicator (somewhere within any cell in the worksheet or on a userform) that will tell me what percentage of the work is in progress in 00.00% format. ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Few more examples that I made to verify accuracy of the macro results. I did this manually which took 3 days and would like to automate this process. ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Example 1. ----------- C: 2 M: =2 F:=9 42 72 Example 2. ----------- C: 3 M: =2 F:=13 4 35 47 Example 3. ----------- C: 3 M: =3 F:=6 10 21 47 10 21 72 10 47 72 21 47 72 4 10 21 4 47 72 47 64 72 Example 4. ----------- C: 5 M: =3 F:=14 4 21 35 47 64 Example 5. ----------- C: 5 M: =4 F:=8 10 21 28 47 72 10 21 47 57 72 15 21 47 64 72 21 47 57 64 72 3 10 21 47 72 Example 6. ----------- C: 5 M: =5 F:=4 10 21 47 64 72 3 4 10 21 69 4 10 21 47 72 4 11 35 69 74 9 10 21 47 72 9 10 21 72 74 Example 7. ----------- C: 10 M: =8 F:=4 9 10 15 21 31 47 64 72 74 75 9 10 13 21 22 49 52 63 64 72 3 4 10 21 33 47 57 60 69 74 3 4 10 21 32 33 37 47 69 74 3 4 10 21 32 33 37 47 60 69 3 4 10 21 32 33 35 37 47 69 3 4 10 11 21 32 33 37 47 69 Example 8. ----------- C: 10 M: =7 F:=7 3 4 7 10 21 47 57 64 72 74 Example 9. ----------- C: 10 M: =6 F:=10 6 9 21 47 49 56 63 64 72 74 7 9 10 11 21 32 47 64 72 74 9 10 11 21 32 47 60 64 72 74 9 10 11 21 35 47 64 69 72 74 9 10 11 21 32 35 47 64 72 74 3 4 7 10 11 35 37 47 64 74 3 4 7 10 32 35 47 64 74 75 3 4 7 32 35 47 64 69 74 75 3 4 7 10 11 32 47 64 74 75 3 4 7 10 35 37 47 64 69 74 3 7 10 32 35 47 64 69 74 75 3 4 21 35 47 57 64 69 72 74 Example 10. ------------ C: 10 M: =10 F:=2 3 4 10 11 21 33 35 60 69 74 4 7 10 21 33 37 47 57 69 75 4 7 9 10 11 32 35 41 69 74 4 6 13 15 35 56 63 64 74 75 4 6 9 12 15 35 47 56 63 72 4 6 9 10 15 21 31 47 72 74 3 10 11 21 32 33 35 60 69 74 3 7 11 33 35 37 41 47 64 75 3 7 10 21 33 37 47 57 69 75 3 7 10 11 35 47 57 60 64 72 3 4 11 21 32 33 35 60 69 74 3 4 10 21 33 37 47 57 69 75 3 4 10 21 32 37 47 64 69 72 3 4 7 10 21 33 37 47 57 69 3 4 10 11 32 33 35 60 69 74 4 9 10 21 33 41 47 57 60 74 3 4 10 11 21 32 35 60 69 74 3 4 10 11 21 32 33 60 69 74 3 4 10 11 21 32 33 35 69 74 3 4 10 11 21 32 33 35 60 74 3 4 10 11 21 32 33 35 60 69 3 4 7 21 33 37 47 57 69 75 3 4 7 10 33 37 47 57 69 75 3 4 7 10 21 37 47 57 69 75 3 4 7 10 21 33 47 57 69 75 3 4 7 10 21 33 37 57 69 75 3 4 7 10 21 33 37 47 69 75 3 4 7 10 21 33 37 47 57 75 3 4 10 21 32 33 35 60 69 74 6 9 12 15 21 31 47 64 74 75 10 16 28 47 51 52 55 64 71 72 9 13 21 22 31 49 52 63 64 75 9 10 21 33 41 47 57 60 72 74 9 10 12 21 22 47 49 52 64 72 6 13 21 22 31 49 52 63 64 75 6 9 21 22 31 49 52 63 64 75 6 9 13 22 31 49 52 63 64 75 6 9 13 21 31 49 52 63 64 75 6 9 13 21 22 49 52 63 64 75 6 9 13 21 22 31 52 63 64 75 6 9 13 21 22 31 49 63 64 75 6 9 13 21 22 31 49 52 64 75 4 7 32 37 47 57 60 64 72 74 6 9 13 21 22 31 49 52 63 64 4 9 10 21 33 41 47 57 60 72 6 9 10 13 21 49 52 63 72 74 4 15 42 45 47 57 60 68 72 74 4 10 21 33 41 47 57 60 72 74 4 10 11 21 32 33 35 60 69 74 4 9 21 33 41 47 57 60 72 74 4 9 10 33 41 47 57 60 72 74 4 9 10 21 41 47 57 60 72 74 4 9 10 21 35 47 64 72 74 75 4 9 10 21 33 47 57 60 72 74 4 9 10 21 33 41 57 60 72 74 4 9 10 21 33 41 47 60 72 74 4 9 10 21 33 41 47 57 72 74 13 15 21 35 47 49 56 63 72 75 6 9 13 21 22 31 49 52 63 75 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused with lots of loops - Please help
Can anybody atleast help me with the first part (Combination)? I can
somehow try and manage the Matches and Frequency. If I enter 3 in the Combinations prompt, it should create 19380 combinations of 3 numbers [ =COMBIN(20,3)*17 ] if I enter 7 in the Combinations promot, it should create combinations of 7 numbers. If the total combinations exceeds 25000 then list only the first 25000 combinations and exit the vba code. Thanks Maxi wrote: Problem : I want to list only those combinations that meets a given criteria. ---------------------------------------------------------------------------------- I need a vba that gives three prompts to the user which asks for Combinations, Matches and Frequency. Lets say the user enters: Combinations (C) : 5 Matches (M) : =4 Frequency (F) : =8 Considering the above criteria, the macro should list combinations of 5 numbers (C) out of which any 4 numbers (M) matches in more than or equal to 8 draws (F). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Confused with lots of loops - Please help
Tom Ogilvy has helped me with the first part (link below) and I am
trying the rest of the code. Will post the code here if I am successful writing the next part of it. http://groups.google.com/group/micro...4bc57c356c729? Thanks Maxi Maxi wrote: Can anybody atleast help me with the first part (Combination)? I can somehow try and manage the Matches and Frequency. If I enter 3 in the Combinations prompt, it should create 19380 combinations of 3 numbers [ =COMBIN(20,3)*17 ] if I enter 7 in the Combinations promot, it should create combinations of 7 numbers. If the total combinations exceeds 25000 then list only the first 25000 combinations and exit the vba code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lots of numbers and columns | Excel Discussion (Misc queries) | |||
Confused by for/next loops | Excel Programming | |||
MS Query uses lots of CPU | Excel Discussion (Misc queries) | |||
lots of data | Excel Discussion (Misc queries) | |||
Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options | Excel Programming |