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

Few more examples

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

Possibly my Keno calc page might speed this process.

[url]http://www.johnph77.com/math/kenocomp.html[/url]

Nothing to sell, information only.

I am aware of all the formulasfor determining total possibilities. What I am looking for is a macro / Excel VBA which can list combinations based on a given criteria.

Hi maheshc,

Perhaps you can Post your Code.
This will have a Two-Fold Effect :-
Firstly, Making it Clearer what Exactly you are Trying to do.
Secondly, Enable us to Try and Help with a Solution.

All the Best.
PAB

Hi maheshc,

All the Best.
PAB

Hi! Pab

I don't have any vba code. I want few excel vba gurus to write a code for me which produces the above results