P

#### Paul Black

I have tried to accomplish the following for about two years now but

without any success.

This is for a 6 numbers drawn from 49 Lotto without replacement.

I have a list of 6 number combinations in an Excel sheet named "Data"

in Cells "B3:G50" ( the combinations will always start in Cell "B3"

BUT the Cell "G50" will change depending on the number of combinations

to evaluate ).

I have a sheet named "Statistics" where the criteria to use is stored.

The criteria to use is as follows :-

Cell "E3" = Total Numbers Drawn ( 6 for example, this could be less or

more ).

Cell "E4" = Total Numbers Selected ( 9 for example, this could be less

or more )

Lets assume that the first 3 combinations are as follows :-

01 02 03 04 05 06

01 02 03 07 08 09

03 05 06 07 08 09

The maximum number used on this occasion is 9. What i would like the

program to do is calculate the unique combinations of 6 numbers from 9

which will be used for the basis of the program.

There are 14 categories of Sets and Subsets for each 6 number

combination. I would like to get a grand total of the combinations

covered for each of the categories below. The grand totals for each

category will go in the sheet named "Statistics" in Cells :-

Cell "D09" = 2 if 2

Cell "D10" = 2 if 3

Cell "D11" = 2 if 4

Cell "D12" = 2 if 5

Cell "D13" = 2 if 6

Cell "D14" = 3 if 3

Cell "D15" = 3 if 4

Cell "D16" = 3 if 5

Cell "D17" = 3 if 6

Cell "D18" = 4 if 4

Cell "D19" = 4 if 5

Cell "D20" = 4 if 6

Cell "D21" = 5 if 5

Cell "D22" = 5 if 6

To achieve this, EACH Set and Subset needs to be run against EACH

combination in turn, starting from the first one in Cells "B3:G3" in

the sheet named "Data" and continuing down.

The 3 if 5 category for example, involves cycling through ALL the 5

number combinations that can be produced from the 9 numbers and

comparing EACH of them with EACH of the combinations in the above list

in turn to see if that particular 5 number combination matches the 5

number combination with *EXACTLY* 3 numbers. If it does, then that

Combination of 3 if 5 is covered and 1 ( One ) is added to that

categories grand total and there is NO need to continue to check for

that particular combinations 3 if 5 cover any further so go onto the

next 3 if 5 combination to check.

********************************************************************************

This is what I found somewhere that might shed some light on what I am

trying to achieve :-

We have a list of combinations C(n,k,t,m)=b where :-

n = the maximum ball number in our list ( e.g. 9 ).

k = the number of balls drawn ( e.g. a 6 ball game has k=6 ).

t = the minimum number we want to guarantee a win ( e.g. 3 ).

m = the condition that has to be met in order to guarantee the t prize

division win, m defines the least number of balls from our n set that

must be correct ( e.g. 5 ).

b = the total tickets required to play.

Now, if you are interested to find the total coverage achieved in a

certain category e.g. "x" if "y", then the total combinations that

need to be covered are nCk(n,y)=A. Thus, you have to test "A"

combinations, each one containing "y" numbers against the combinations

in the list ( each combination contains k numbers ).

A combination of those "A" is covered if there is at least one

combination in your list, that contains at least "x" numbers in

common. All you have to do is to go through all "A" combinations and

test each of them to see if it contains at least "x" numbers in common

with at least one combination in your list of combinations. If it

does, then it is covered.

********************************************************************************

I have made a start on the programming ( probably not the best way to

write this ) ...

Option Explict

Option Base 1

Sub Produce_Statistics()

Dim A as Integer

Dim B as Integer

Dim C as Integer

Dim D as Integer

Dim E as Integer

Dim F as Integer

Dim MinVal As Integer

Dim MaxVal As Integer

Application.ScreenUpdating = False

MinVal = 1

MaxVal = WorkSheets.("Statistics").Range("E4").Value

For A = 1 to MaxVal - 5

For B = A + 1 to MaxVal - 4

For C = B + 1 to MaxVal - 3

For D = C + 1 to MaxVal - 2

For E = D + 1 to MaxVal - 1

For F = E + 1 to MaxVal

*** Code goes here maybe ***

Next F

Next E

Next D

Next C

Next B

Next A

Application.ScreenUpdating = True

End Sub

I am new to VBA so have no idea how to accomplish this.

Thanks in Advance.

All the Best.

Paul