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 |
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 |