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