Calculate Sets and Subsets

[Paul Black]

Hi everyone,

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

 

[Mike H]

Paul,

I did this years ago in quick basic when the UK lottery started and here's
the start of the conversion to Excel. This gives every possible combination
of 6 from x numbers. (x is the variable fullset). At the moment it uses the
numbers 1 - x and perms any 6 from those but it should be fairly
straightforward to make it use any 10 numbers a user enters but i've run out
of time right now. Hope this gets you started.

Sub permit()
Dim fullset As Integer
Dim maxpossible As Long
Dim found() As String
Dim n As Long, n1 As Long, n2 As Long, n3 As Double
Dim n4 As Long, n5 As Long, n6 As Double
fullset = 12
maxpossible = 65535
ReDim found(maxpossible)
n = 1
For n1 = 1 To fullset
For n2 = 2 To fullset
If n2 > n1 Then
For n3 = 3 To fullset
If n3 > n2 Then
For n4 = 4 To fullset
If n4 > n3 Then
For n5 = 5 To fullset
If n5 > n4 Then
For n6 = 6 To fullset
If n6 > n5 Then
If (n6 <> n5) And (n5 <> n4) And (n4 <> n3) And (n3 <> n2) And
(n2 <> n1) Then
found(n) = CStr(n1) & "," & CStr(n2) & "," & CStr(n3) & ","
& CStr(n4) & "," & CStr(n5) & "," & CStr(n6)
n = n + 1
End If
End If
Next n6
End If
Next n5
End If
Next n4
End If
Next n3
End If
Next n2
Next n1
For n = 1 To maxpossible
Cells(n, 1).Value = found(n)
Next n
End Sub

Mike

 

[Paul Black]

Thanks for the reply Mike,

I think my previous explanation was ambiguous and has confused what I
am trying to achieve.
Please find below what I hope to be a simpler and more understandable
explanation of what I would like to achieve.
I have a list of 6 number combinations in an Excel sheet named "Data"
in Cells "B3:G50". This range will vary depending on the number of
combinations to be evaluated, but the first 6 number combination will
always start in Cells "B3:G3".
I have a sheet named "Statistics" where the criteria to use is stored
and is as follows :-

Cell "E3" = The total numbers drawn ( which is 6 ).
Cell "E4" = The highest number used in any of the 6 number
combinations ( which is 9 in this example ).

For the purpose of this program, there will always be 6 number
combinations in the list to evaluate. The highest number in any of the
6 number combinations in the list can be anything from 6 to "?" in
future evaluations.
Now lets say that we want to calculate the total combinations covered
for categories ...

2 if 5 - The answer will go in Cell "D12" in the sheet named
"Statistics".
3 if 5 - The answer will go in Cell "D16" in the sheet named
"Statistics".
4 if 5 - The answer will go in Cell "D19" in the sheet named
"Statistics".
5 if 5 - The answer will go in Cell "D21" in the sheet named
"Statistics".

.... for the combinations ...

01 02 03 04 05 06
01 02 03 07 08 09
03 05 06 07 08 09

Now comes the BONES of the program.
We need to produce ( in memory maybe ) ALL the 5 number combinations
from 9 ( 9 being the highest number in any of the 6 number
combinations in the list in this instance ) and compare EACH of them
in turn to EACH 6 number combination in the list in turn. If at
"LEAST" 2 numbers in any 5 number combination matches 2 numbers in any
6 number combination then that 5 number combination satisfies the 2 if
5 scenario and 1 ( one ) is added to the total combinations covered
for the 2 if 5 category and you can STOP checking that particular 5
number combination for the 2 if 5 scenario and go onto the next 5
number combination.
The same principle applies to the 3 if 5, 4 if 5 & 5 if 5 categories,
so what could be done is while you are cycling through the 5 number
combinations for the 2 if 5 scenario you could also check for the ...

At "LEAST" 3 if 5 match
At "LEAST" 4 if 5 match
At "LEAST" 5 if 5 match

.... scenarios and keep a total count for those as well. This way you
only need to cycle through the 5 number combinations once. The
important thing is that once a scenario has been met in any 5 number
combination then you can STOP checking for that particular scenario
because we only want to have one instance to be added to the
respective category total.

Therefore the code for the above to produce the 5 number combinations
would be something like this :-

Option Explicit
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 MinVal As Integer
Dim MaxVal As Integer

Application.ScreenUpdating = False

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

For A = 1 to MaxVal - 4
For B = A + 1 to MaxVal - 3
For C = B + 1 to MaxVal - 2
For D = C + 1 to MaxVal - 1
For E = D + 1 to MaxVal

*** Code goes here maybe ***

Next E
Next D
Next C
Next B
Next A

Application.ScreenUpdating = True
End Sub

I hope this makes it clearer.
Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi everyone,

The highest number used in this example is 9 ( this could be anything
from 6 to "?" in future evaluations ), but ALL COMBIN(9,5)
combinations must be produced and tested against ALL the 6 number
combinations in the list for each of the categories 2 if 5, 3 if 5, 4
if 5 and 5 if 5.
Basically, as you loop through each 5 number combination ( because no
data needs to be written to the spreadsheet other than the categories
total once it has finished evaluating ), you compare it to each of the
6 number combinations to see how many numbers are matched.
If we use the 5 number combinations as "x" for example, and the 6
number combinations as "y" for example, this is roughly what should
happen :-

If x matches y in >= 2 numbers Then
Category 2 if 5 Total = + 1
If x matches y in >= 3 numbers Then
Category 3 if 5 Total = + 1
If x matches y in >= 4 numbers Then
Category 4 if 5 Total >= + 1
If x matches y in EXACTLY 5 numbers Then
Category 5 if 5 Total = + 1
End If
End If
End If
End If

The important thing is that once a scenario ( there are five scenarios
to be evaluated for the x if 5 categories ) has been met in any 5
number combination then you can STOP checking for that particular
scenario because we only want to have one instance for each scenario
to be added to the respective category total.

Hope this explains it a bit better.
Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi everybody,

Any help or suggestions will be greatly appreciated.

Thanks in Advance.
All the Best.
Paul