Confused with lots of loops - Please help

M

Maxi

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,68,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,71,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,60,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,64,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,dID7,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
 
M

Maxi

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
 
L

lexcel

I posted yet another combination generator in your other post, but I
think that if I understand what you want I can come up with a lot
simpler and more efficient approach. I need to sleep over it, though
and have ample time the coming 2 days. Let me know if you are
interested.

Good luck,

Lex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top