# Thread: Calculate Sets and Subsets

1. ## Calculate Sets and Subsets

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

Code:
```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.

All the Best.
PAB1

2. ## Re: Calculate Sets and Subsets

Excel VBA question moved to Office Development

3. ## Re: Calculate Sets and Subsets

I don't follow.

Could you explain it a different way? Fewer words would be good.

4. ## Re: Calculate Sets and Subsets

Thanks for the reply Ellis Dee,

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

Code:
```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.
All the Best.
PAB1

5. ## Re: Calculate Sets and Subsets

This doesn't really make sense, but you've explained it well enough I suppose.

From what are you making your permutations? Let's say you have the three 6-number combinations from the OP:

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

You say you want to combine all numbers up to 9, since 9 in the highest number. That means 1 though 9, and all is well. But what if instead your three combinations are:

01 02 03 04 05 06
01 02 03 04 05 08
01 02 03 04 05 10

Given this set, do you want all numbers less than or equal to 10, or do you want all numbers found in one of the combinations? In other words, should 07 and 09 be included?

6. ## Re: Calculate Sets and Subsets

Incidentally, for permutations in general, here's a solution I originally posted for a different thread. The premise is that the order doesn't matter, so xyz is considered the same as zyx.
Code:
```Public Sub CombineNumbers(ParamArray args())
Dim i As Long
Dim iMax As Long

iMax = UBound(args)
If iMax <> -1 Then
For i = 0 To iMax
CombineNumbersRecurse args, Null, 0, iMax, i
Next
End If
End Sub

Private Sub CombineNumbersRecurse(ByVal pvar As Variant, pvarPrefix As Variant, plngMin As Long, plngMax As Long, plngLevel As Long)
Dim i As Long

For i = plngMin To plngMax
If plngLevel = 0 Then
Debug.Print pvarPrefix & pvar(i)
Else
CombineNumbersRecurse pvar, pvarPrefix & pvar(i), i + 1, plngMax, plngLevel - 1
End If
Next
End Sub```
This also works for strings. Sample output:

CombineNumbers "a", "b", "c"
a
b
c
ab
ac
bc
abc

CombineNumbers 5, 4, 9, 1
5
4
9
1
54
59
51
49
41
91
549
541
591
491
5491

7. ## Re: Calculate Sets and Subsets

Thanks for the reply Ellis Dee.

I am dealing purely with combinations and not permutations.
The 6 number combinations in the list represents a Wheel that Lotto players use. I want to produce the total covered statistics for each category for that Wheel.
There will always be 6 number combinations in the list to be evaluated.
Although some of the numbers aren't used in the 6 number combination list it does not make a difference.
I used the highest number as 9 ( this could be anything from 6 to "?" in future evaluations ) for an example, 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 ), 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 :-

Code:
```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```
Hope this explains it a bit better.
All the Best.
PAB1

8. ## Re: Calculate Sets and Subsets

What is the spread for the individual lottery numbers?

Expanding on my earlier question, if you want to include 07 and 09, why aren't you including any numbers higher than 10?

9. ## Re: Calculate Sets and Subsets

Thanks for the reply Ellis Dee.

The numbers used in the Wheel which are in the sheet named "Data" and in Cells "B3:G?" are pointer numbers which will eventually be replaced by actual playing numbers. I have formulas that apply the conversion already setup to do this.
The Wheel can have ANY numbers fom 1 to 9 in ANY of the 6 number combinations, but each 6 number combination must have 6 unique numbers ( i.e. no number appears more than once in any 6 number combination ).
Because the highest number is 9, although not all numbers from 1 to 9 need be used in the Wheel, the program loop still needs to accomodate these to evaluate the categories. The reason being, that another Wheel may well include those numbers as well as the highest number being greater than 9, thats why it gets the highest number in the Wheel from Cell "E4" in the sheet named "Statistics" because it can be different for another Wheel.

Hope this Helps.
All the Best.
PAB1

10. ## Re: Calculate Sets and Subsets

Ellis:

How should I pass to the function combinenumbers? I tried the following but it did not work. Please help.

Private Sub getcombinations()
Dim strNeigh As String
Dim arrNeigh() As String
strNeigh = "1;2;3;4"
arrNeigh = Split(strNeigh, ";")
CombineNumbers arrNeigh

End Sub

Thanks

11. ## Re: Calculate Sets and Subsets

It's just an example; you'll have to tailor it to use an actual array instead of a parameter array. I provided example usage in the post:

CombineNumbers "a", "b", "c"
CombineNumbers 5, 4, 9, 1

I'd like to help further but I still can't really understand what it is you're trying to do. Instead of explaining in narrative form, could you offer a streamlined, concrete example from start to finish, including what the input is and what the output should be? Just use 3 numbers instead of six, and keep the legal number range small.

12. ## Re: Calculate Sets and Subsets

Oops, sorry, thought you were the OP.

13. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

I manually input combinations of say 3 numbers ...

Combination ( 1 ) - Cells B3:D3 = Combination 01 02 03
Combination ( 2 ) - Cells B4:D4 = Combination 01 02 04
Combination ( 3 ) - Cells B5:D5 = Combination 01 03 05

... in an Excel sheet named "Data" in Cells "B3:D?" or whatever.

In a sheet named "Statistics" in Cell "D10" I would like to have the total combinations covered for the 2 if 3 category.
This requires calculating ALL the combinations available ( Equivalent to Excels COMBIN(5,3) formula ) in memory and then comparing each one to each combination in the sheet named "Data" to see if there is at LEAST 2 numbers in common. If there is then that particular combination is covered and 1 is added to the total and there is no need to check the rest of the cominations for that particular 2 if 3 scenario.

All the combinatios for 3 numbers from 5 are as follows ...

( A ) 3 number = 01 02 03 produces 2 number = 01 02, 01 03, 02 03
( B ) 3 number = 01 02 04 produces 2 number = 01 02, 01 04, 02 04
( C ) 3 number = 01 02 05 produces 2 number = 01 02, 01 05, 02 05
( D ) 3 number = 02 03 04 produces 2 number = 02 03, 02 04, 03 04
( E ) 3 number = 02 03 05 produces 2 number = 02 03, 02 05, 03 05
( F ) 3 number = 03 04 05 produces 2 number = 03 04, 03 05, 04 05

... so ...

( A ) 01 02 is covered in combination ( 1 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover although 01 02 is also covered in combination ( 2 ).
( A ) 01 03 is covered in combination ( 1 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover although 01 03 is also covered in combination ( 3 ).
( A ) 02 03 is covered in combination ( 1 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover ( not covered anywhere else anyway ).

( B ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it because is was covered in ( A ) and 1 has already been added to the total.
( B ) 01 04 is covered in combination ( 2 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover ( not covered anywhere else anyway ).
( B ) 02 04 is covered in combination ( 2 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover ( not covered anywhere else anyway ).

( C ) 01 02 is covered in combination ( 1 & 2 ) but we ignore it because is was covered in ( A ) and 1 has already been added to the total.
( C ) 01 05 is covered in combination ( 3 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover ( not covered anywhere else anyway ).
( C ) 02 05 is NOT covered anywhere and so is ignored all together.

( D ) 02 03 is covered in combination ( 1 ) but we ignore it because is was covered in ( A ) and 1 has already been added to the total.
( D ) 02 04 is covered in combination ( 2 ) but we ignore it because is was covered in ( B ) and 1 has already been added to the total.
( D ) 03 04 is NOT covered anywhere and so is ignored all together.

( E ) 02 03 is covered in combination ( 1 ) but we ignore it because is was covered in ( A ) and 1 has already been added to the total.
( E ) 02 05 is NOT covered anywhere and so is ignored all together.
( E ) 03 05 is covered in combination ( 3 ) so 1 is added to the total for 2 if 3 and we can stop checking for that cover ( not covered anywhere else anyway ).

( F ) 03 04 is NOT covered anywhere and so is ignored all together.
( F ) 03 05 is covered in combination ( 3 ) but we ignore it because is was covered in ( E ) and 1 has already been added to the total.
( F ) 04 05 is NOT covered anywhere and so is ignored all together.

... which means that out of the 10 combinations tested, 7 are covered for the 2 if 3 category.
It would be useful to have the total combinations tested which could go in Cell "C10".

Hope this Helps.
All the Best.
PAB1

14. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

I think I have totally confused the concept of what I am trying to achieve.
I would like an algorithm or code to work out how many combinations are covered when a subset (with specific criteria) of a set is compared with combinations in a wheel. A wheels criteria is as follows :-

Description = C(n,k,t,m)=b where :-

n=the number of balls drawn from (i.e. 49).
k=the number of balls drawn (i.e. 6).
t=the total balls to match to guarantee a win.
m=the criteria that has to be met in order to guarantee t win, m defines the least number of balls from our n set that must be correct.
b=the total combinations in the wheel.

The formula to calculate the total combinations is :-
........................n!
C(n, k) = -------------------
..................k! (n - k) !

The formula to calculate the subsets (the coefficient) is ...
.........................n!
C(n, m) = -------------------
..................m! (n - m) !

... where n! is n factorial and is equal to n*(n-1)*(n-2)* ... *2*1.

The above was taken from :-
http://mathforum.org/library/drmath/view/60881.html

I want to work out what the coverage of a wheel is for ALL the following categories please, ...

2 if 2
2 if 3
2 if 4
2 if 5
2 if 6
2 if 7
3 if 3
3 if 4
3 if 5
3 if 6
3 if 7
4 if 4
4 if 5
4 if 6
4 if 7
5 if 5
5 if 6
5 if 7
6 if 6
6 if 7

... these categories apply to any wheel that is tested.
The category "2 if 5" means, that "IF" at "LEAST" 2 numbers in ANY of the 5 number combinations matches at "LEAST" 2 numbers in ANY of the 6 number combinations in the wheel (input in the spreadsheet by me) then those 5 number combinations have satisfied the "2 if 5" scenario and are therefore covered. You then add 1 to the category "2 if 5" total for each of the 5 number combinations that has satisfied the 2 if 5 scenario.
For the ? if 5 scenario, there is NO need to list the C(n,5) combinations because I ONLY want the total combinations covered for each category, not the actual combinations themselves. Anyway, the 5 number combinations only need to be calculated for the highest "n" number in the wheel, this could be 12 [ C(12,5) ], 15 [ C15,5) ], 20 [ C(20,5) ], 30 [ (30,5) ] etc.
Basically, I think the program needs to iterate through the 5 number combinations and check for each ? if 5 scenario ( 2 if 5, 3 if 5, 4 if 5 and 5 if 5 ) against each 6 number combination in the spreadsheet. If it satisfies ANY of the ? if 5 scenarios then 1 is added to the respective category covered total.

I hope this makes it a bit clearer.

All the Best.
PAB1

15. ## Re: Calculate Sets and Subsets

Most people's eyes (including mine) glaze over after a few paragraphs. Let's back up and break this into very small chunks. Don't worry about getting everything done at once. What is the very first step you need help with? What exact combinations do you need to generate?

Remember, just one small piece at a time...

16. ## Re: Calculate Sets and Subsets

Thanks Ellis Dee for the reply,

Disregard everything that went before and lets start from scratch.
I would like an algorithm or code to work out how many subset combinations with specific criteria are covered when compared with combinations in a wheel. A wheels criteria is as follows :-

Description = C(n,k,t,m)=b where :-

n=the number of balls drawn from (i.e. 49).
k=the number of balls drawn (i.e. 6).
t=the total balls to match to guarantee a win.
m=the criteria that has to be met in order to guarantee t win, m defines the least number of balls from our n set that must be correct.
b=the total combinations in the wheel.

The formula to calculate the total combinations is :-
........................n!
C(n, k) = -------------------
...................k! (n - k) !

The formula to calculate the subsets (the coefficient) is ...
........................n!
C(n, m) = -------------------
...................m! (n - m) !

... where n! is n factorial and is equal to n*(n-1)*(n-2)* ... *2*1.

The above was taken from :-
http://mathforum.org/library/drmath/view/60881.html

I manually input combinations of 6 numbers into a spreadsheet named "Data" in cells "B3:G?".
We will use the 2 if 5, 3 if 5, 4 if 5 and 5 if 5 categories to explain what I am trying to achieve.
For the 2 if 5 category, this requires calculating ALL the 5 number combinations available from "n" which would be C(n, k) where "n" is 49 (or the maximum number used in the wheel) and "k" is 5.
The category 2 if 5 means, that "IF" at "LEAST" 2 numbers in ANY of the 5 number combinations matches 2 numbers in ANY of the 6 number combinations in the wheel, then those 5 number combinations have satisfied the 2 if 5 scenario and are therefore covered. You then add 1 to the 2 if 5 category total for each of the 5 number combinations that has satisfied the 2 if 5 scenario.
For the 2 if 5 scenario, there is NO need to list the C(n,5) combinations because I ONLY want the total combinations covered for the 2 if 5 category, not the actual combinations themselves. Anyway, the 5 number combinations ONLY need to be calculated for the highest "n" number in the wheel, this could be 12 [ C(12,5) ], 15 [ C15,5) ], 20 [ C(20,5) ], 30 [ (30,5) ] etc.
Basically, the program needs to iterate through ALL the 5 number combinations from "n" and check each one to see if there is at "LEAST" 2 numbers in ANY of the 6 number combinations in the spreadsheet. If there is, 1 is added to the category covered total for each 5 number combination that satisfies the 2 if 5 scenario.
The same principle and structure applies for the 3 if 5, 4 if 5 and 5 if 5 categories.
I would like the total combinations covered for the 2 if 5 category to go in Cell "D12" in the sheet named "Statistics".

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

Code:
```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```
I input the 6 number combinations in the sheet named "Data" and in the Range "B3:G?".
The number of 6 number combinations could be whatever, so I think that we would need to use the xlup function at some stage for this.
We then need to find out what the highest number used is in the Range "B3:G?".
Then we need to produce the combinations necessary for C(n,5) where "n" is the highest number in the Range "B3:G?".
These 5 number combinations can be calculated and held or stored in memory because I ONLY want the total combinations covered for the 2 if 5 category, not the actual combinations themselves.

Maybe the code to produce the 5 number combinations would look something like this :-

Code:
```Option Explicit
Option Base 1

Sub Produce_5_Number_Combinations()

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 = Whatever the highest number in the sheet named "Data" and in the Range "B3:G?" is.

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 a bit clearer.

All the Best.
PAB1

17. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

Most people's eyes (including mine) glaze over after a few paragraphs.
What is the very first step you need help with?.
What exact combinations do you need to generate?.
I will give this request one last try and put it as simply as I can without all the confusing and complicated jargon.
I have the following wheel of 6 number combinations in Cells "B3:G12" in a sheet named "Data".

01 02 03 04 05 06
01 07 08 09 10 11
01 12 13 14 15 16
02 03 07 08 12 13
02 03 09 10 14 15
04 05 07 08 14 15
04 05 09 10 12 13
02 03 04 05 11 16
06 07 08 09 10 16
06 11 12 13 14 15

I want to iterate through ( see the code below ) ALL the 5 number combinations produced from "n" ( "n" = the highest number used in ANY of the 6 number combinations which is 16, so C(16,5) = 4,368 combinations ) and compare each 5 number combination with each 6 number combination in the wheel above.

If at "LEAST" 2 numbers match then add 1 to the 2 if 5 category total ( total to go in Cell "D12" in the sheet named " Statistics" ).
If at "LEAST" 3 numbers match then add 1 to the 3 if 5 category total ( total to go in Cell "D16" in the sheet named "Statistics" ).
If at "LEAST" 4 numbers match then add 1 to the 4 if 5 category total ( total to go in Cell "D19" in the sheet named "Statistics" ).
If ALL 5 numbers match then add 1 to the 5 if 5 category total ( total to go in Cell "D21" in the sheet named "Statistics" ).

If ANY of the 5 number combinations does NOT match ANY of the 6 number combinations with the required scenario of ? if 5, then that 5 number combination is NOT covered for that particular scenario of ? if 5 and therefore 1 ( one ) cannot be added to the respective ? if 5 category total.

That is it in a nutshell really.

I have written the following code which might be of use :-

Code:
```Option Explicit
Option Base 1

Sub Produce_5_Number_Combinations()

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 = Whatever the highest number In the sheet named "Data" And In the Range "B3:G?" is.

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

*** Output totals For Each category ***

Application.ScreenUpdating = True
End Sub```
All the Best.
PAB1

18. ## Re: Calculate Sets and Subsets

Excellent, I can work with that. (No doubt many others can as well.) I'm actually in the middle of something right now, but I'll take a crack at it in the next couple days.

I can tell you right now that you won't want to used nested For...Next loops, since that effectively hardcodes the number of numbers at six. A more elegant solution would either use a master control loop with some kind of variable control structure like a dynamic array, or the even more elegant recursive function.

I happen to enjoy writing solutions to problems like these, so there is a very high probability I'll post a solution for you sometime this weekend.

19. ## Re: Calculate Sets and Subsets

Thank you SO much Ellis Dee,

I have tried to accomplish this for about 2 or 3 years but without ANY success. I am glad that I have finally been able to explain what it is I am actually after.
It would be very much appreciated if you could include little notes in the code as to what each bit of the code is doing as well please, me not being a programmer and new to VBA and all that.
The program will also needs to iterate through ALL the combinations of "n" and compare the ? if ? scenarios for ...

6 number combinations
5 number combinations - As used in my example
4 number combinations
3 number combinations
2 number combinations

... as well.

All the Best.
PAB1

20. ## Re: Calculate Sets and Subsets

Yeah, that's what I figured, which is why I say you won't want to use nested for loops.

21. ## Re: Calculate Sets and Subsets

I started messing with this and am making decent progress. I notice a contradiction in a couple of your posts: (bolding mine)
Originally Posted by PAB1
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 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.
Holy run-on sentences, Batman!

Is it *EXACTLY* or *AT LEAST*?

22. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

Is it *EXACTLY* or *AT LEAST*?
I am pretty sure it is at "LEAST". I will look back over my notes and confirm.
Does it make a great deal of difference?.

All the Best.
PAB1

23. ## Re: Calculate Sets and Subsets

Not right now, no, because I'm still generating combinations. I haven't gotten to the part where it compares them against the numbers.

It will add complexity to make it work both ways, allowing the user to choose. I'm not sure how much complexity; if it's no big deal I'll make it flexible. But just in case, it would be good to know what your first choice is.

24. ## Re: Calculate Sets and Subsets

Oh, btw, do you already have a spreadsheet set up for this, with the layout as you like it? If so, attach it to a post and I'll incorporate the code directly into that.

25. ## Re: Calculate Sets and Subsets

I finished it; you're all set. Download this spreadsheet and go nuts. Note that it will likely take a very long time to calculate because it is logging every calculation to a text file for debugging/verification purposes. Set the log file constant to False to greatly speed it up.

The code is very long; I'll break it up into the next couple posts.

I decided to go with *EXACTLY*, btw.

26. ## Re: Calculate Sets and Subsets

Module-level declarations:
vb Code:
`Option Explicit Private Const UseLogFile = True ' Dump all info to Log.txt as it's generated; turn off to greatly increase speed Private Const StatsSheet = "Statistics"Private Const DataSheet = "Data"Private Const DataTopLeft = "B3"Private Const OutputStart = "D9"Private Const NumbersDrawn = "E3"Private Const NumbersSelected = "E4"Private Const ShowCaptions = True ' Shows descriptions one column left of data Private mlngRows As LongPrivate mlngCols As LongPrivate mlngOutputRow As LongPrivate mlngOutputColumn As Long Private mstrLogFile As String ' Log file path & filenamePrivate FileNumber As Long ' File handle for log file`
Main program entry point:
vb Code:
`' Main entry point - called from the command button' (Code for command button is in Sheet2)Public Sub CalculateWheel()    Dim strRange As String    Dim varData As Variant ' 2-dimensional array holding all selected numbers    Dim lngComb() As Long ' 2-dimensional array holding combinations    Dim x As Long    Dim y As Long    Dim lngMax As Long ' Highest selected value    Dim sngStart As Single        sngStart = Timer    ' Identify where output begins. This will fail if you specify a    ' column past column Z. Note that the Asc() function returns a    ' numeric character code. Asc("A")=65, Asc("B")=66, etc...    ' Each subsequent call simply moves down one row.    mlngOutputRow = Mid(OutputStart, 2)    mlngOutputColumn = Asc(Left(OutputStart, 1)) - 64    If UseLogFile Then        ' Get full path and filename of active workbook        mstrLogFile = ActiveWorkbook.FullName        ' Use same path & name, but change extension to ".log"        mstrLogFile = Left(mstrLogFile, InStrRev(mstrLogFile, ".")) & "log"        ' Get available file handle        FileNumber = FreeFile()        ' Create log file (overwrite existing)        Open mstrLogFile For Output As #FileNumber    End If    ' Identify data block (see constants defined at top of module)    Sheets(StatsSheet).Select    ' Check spreadsheet values; if found, use them...    If Range(NumbersSelected).Value <> 0 And Range(NumbersDrawn).Value <> 0 Then        x = Asc(Left(DataTopLeft, 1)) + Range(NumbersDrawn).Value - 65        y = Val(Mid(DataTopLeft, 2)) + Range(NumbersSelected).Value - 1        strRange = DataTopLeft & ":" & GetColumnLetter(x) & y    Else ' ...otherwise just use all data found        Range(DataTopLeft).Select        ActiveCell.SpecialCells(xlLastCell).Select        strRange = DataTopLeft & ":" & GetColumnLetter(ActiveCell.Column) & ActiveCell.Row    End If    Sheets(DataSheet).Select    Range(strRange).Select    ' Load selected numbers into array    varData = Range(strRange)    mlngRows = UBound(varData, 1)    mlngCols = UBound(varData, 2)    ' Identify largest value by comparing against every value in matrix    For x = 1 To mlngRows        For y = 1 To mlngCols            If varData(x, y) > lngMax Then lngMax = varData(x, y)        Next    Next    If UseLogFile Then        Print #FileNumber, "Raw data (Highest number: " & lngMax & ")" & vbNewLine & "--------"        DisplayArray varData, True    End If    ' Generate all the combinations of 2 through mlngCols numbers    For y = 2 To mlngCols        ' Store all 2-, 3-, 4- or 5-number combinations to lngComb()        GetCombinations lngComb, lngMax, y        If UseLogFile Then            If y <> 2 Then Print #FileNumber, ""            Print #FileNumber, UBound(lngComb, 2) + 1 & " combinations of " & y & " numbers" & vbNewLine & String(Len(UBound(lngComb, 2) + 1 & " combinations of " & y & " numbers"), 45)        End If        ' Locate matches        IdentifyMatches varData, lngComb, y        ' Free memory        Erase lngComb    Next    If UseLogFile Then Close #FileNumber    ' Free memory    Erase varData    Sheets(StatsSheet).Select    MsgBox "Processing completed successfully in " & SecondsToTime(Timer - sngStart) & " seconds", vbInformation, "Notice"End Sub`
Miscellaneous utility functions:
vb Code:
`' Return column letter given a column numberPrivate Function GetColumnLetter(plngColumn As Long) As String    ' This function will fail past column Z, but that shouldn't    ' matter for this application. Note that Chr(65) = "A"    GetColumnLetter = Chr(64 + plngColumn)End Function ' Used for debug purposes during developmentPrivate Sub DisplayArray(pvarArray As Variant, pblnByRow As Boolean)    Dim x As Long    Dim y As Long        If pblnByRow Then        For x = LBound(pvarArray, 1) To UBound(pvarArray, 1)            For y = LBound(pvarArray, 2) To UBound(pvarArray, 2)                Print #FileNumber, Format(pvarArray(x, y), "00"); " ";            Next            Print #FileNumber, ""        Next        Print #FileNumber, ""    Else        For y = LBound(pvarArray, 2) To UBound(pvarArray, 2)            For x = LBound(pvarArray, 1) To UBound(pvarArray, 1)                Print #FileNumber, Format(pvarArray(x, y), "00"); " ";            Next            Print #FileNumber, ""        Next        Print #FileNumber, ""    End IfEnd Sub ' Returns "0:12", "14:26", "6:00:12", etc...Private Function SecondsToTime(ByVal psngSeconds As Single) As String    Dim lngSeconds As Long    Dim strReturn As String        lngSeconds = Int(psngSeconds)    If lngSeconds >= 3600 Then        strReturn = lngSeconds \ 3600        lngSeconds = lngSeconds Mod 3600        strReturn = strReturn & ":" & Format(lngSeconds \ 60, "00") & ":" & Format(lngSeconds Mod 60, "00")    ElseIf lngSeconds > 59 Then        strReturn = lngSeconds \ 60 & ":" & Format(lngSeconds Mod 60, "00")    Else        strReturn = Format(lngSeconds, "0")    End If    SecondsToTime = strReturn & Format(psngSeconds - lngSeconds, ".0")End Function`

27. ## Re: Calculate Sets and Subsets

Generate combinations:
vb Code:
`' Combination generator engine' plngMax: Highest number from matrix' plngNumbers: Number of elements in each combinationPrivate Sub GetCombinations(plngComb() As Long, plngMax As Long, plngNumbers As Long)    Const ArrayBuffer = 128 ' Used to speed up memory allocation    Const Current = 1 ' Current element    Const Minimum = 2 ' Value used during rollover    Const Maximum = 3 ' Maximum possible value    Dim lngNum() As Long ' Array to hold individual number indexes (not combinations)    Dim lngArraySize As Long ' Size of combinations array    Dim lngLastCombination As Long ' Array position of last combination    Dim i As Long    Dim j As Long     ' Initialize combinations array    lngArraySize = ArrayBuffer - 1    ReDim plngComb(1 To plngNumbers, 0 To lngArraySize)    ' If, for example, we're looking for 3-number combinations,    ' this array will have 3 elements and initialize to { 1,2,3 }    ReDim lngNum(1 To 3, 1 To plngNumbers)    For i = 1 To plngNumbers        ' Current element index        lngNum(Current, i) = i        ' Min value this element can have. Starts as min1=1, min2=2, etc...        ' Gets incremented dynamically each rollover        lngNum(Minimum, i) = i        ' Max value this element can have. For 3-element combinations        ' with max value 16, max1 = 14, max2 = 15, max3 = 16        lngNum(Maximum, i) = plngMax - (plngNumbers - i)        ' Pre-stuff current combination        plngComb(i, lngLastCombination) = i    Next    ' Main loop    Do        ' Identify right-most element that can increment without rollover        For i = plngNumbers To 1 Step -1            If lngNum(Current, i) < lngNum(Maximum, i) Then Exit For        Next        ' If no element can increment, we're done        If i = 0 Then Exit Do        ' Increment all elements from i to last        For j = i To plngNumbers            If lngNum(Current, j) < lngNum(Maximum, j) Then                lngNum(Current, j) = lngNum(Current, j) + 1            Else ' Rollover; increment minimum                If j = 1 Then                    lngNum(Minimum, j) = lngNum(Minimum, j) + 1                Else                    lngNum(Minimum, j) = lngNum(Current, j - 1) + 1                End If                lngNum(Current, j) = lngNum(Minimum, j)            End If        Next        ' Allocate space to save this combination        lngLastCombination = lngLastCombination + 1        If lngLastCombination > lngArraySize Then            lngArraySize = lngArraySize + ArrayBuffer            ReDim Preserve plngComb(1 To plngNumbers, 0 To lngArraySize)        End If        ' Save this combination        For i = 1 To plngNumbers            plngComb(i, lngLastCombination) = lngNum(Current, i)        Next    Loop    ' Free memory    ReDim Preserve plngComb(1 To plngNumbers, 0 To lngLastCombination)    Erase lngNumEnd Sub`
Compare the combinations against the set of numbers from the data sheet:
vb Code:
`Private Sub IdentifyMatches(pvarData As Variant, plngComb() As Long, plngNumbers As Long)    Dim x As Long    Dim i As Long    Dim lngCombinations As Long    Dim lngFound() As Long ' Array holding hits    Dim blnFound() As Boolean ' Flags preventing duplicate counts    Dim lngMatches As Long        ' Save array boundary to variable to speed execution    lngCombinations = UBound(plngComb, 2)    ' Initialize hits arrays    ReDim lngFound(2 To plngNumbers)    ReDim blnFound(2 To plngNumbers)    ' Step through each generated combination    For i = 0 To lngCombinations ' Combinations start at 0, not 1        ' Step through each entered data combination        For x = 1 To mlngRows            ' Count number of matches            lngMatches = CountMatches(pvarData, x, plngComb, i, plngNumbers)            ' Tally buckets            If lngMatches > 1 Then                If Not blnFound(lngMatches) Then                    blnFound(lngMatches) = True                    lngFound(lngMatches) = lngFound(lngMatches) + 1                End If            End If        Next        ' Reset flags        For x = 2 To plngNumbers            blnFound(x) = False        Next    Next    ' Display results    Sheets(StatsSheet).Select    For i = 2 To plngNumbers        If ShowCaptions Then            Cells(mlngOutputRow, mlngOutputColumn - 1).Value = i & " if " & plngNumbers        End If        Cells(mlngOutputRow, mlngOutputColumn).Value = lngFound(i)        mlngOutputRow = mlngOutputRow + 1        Range(GetColumnLetter(mlngOutputColumn) & mlngOutputRow).Select    Next    ' Free memory    Erase blnFound, lngFoundEnd Sub ' Find matches for a given data row, combination and bucket sizePrivate Function CountMatches(pvarData As Variant, plngRow As Long, plngComb() As Long, plngCombination As Long, plngNumbers As Long) As Long    Dim y As Long    Dim i As Long    Dim lngMatches As Long        For i = 1 To plngNumbers        For y = 1 To mlngCols            If plngComb(i, plngCombination) = pvarData(plngRow, y) Then                lngMatches = lngMatches + 1                Exit For            End If        Next    Next    CountMatches = lngMatches    If UseLogFile Then        For i = 1 To plngNumbers            If i = 1 Then Print #1, "C("; Else Print #1, ",";            Print #FileNumber, Trim\$(plngComb(i, plngCombination));        Next        For y = 1 To mlngCols            If y = 1 Then Print #1, ")=" & lngMatches & " for";            Print #FileNumber, " " & Format(pvarData(plngRow, y), "00");        Next        Print #1, ""    End IfEnd Function`

28. ## Re: Calculate Sets and Subsets

Let me know if you have any questions.

29. ## Re: Calculate Sets and Subsets

Thanks SOO much Ellis Dee,

I do not have access to Excel for a couple of days so I will not be able to run your code and see what the output it poduces is like.
I will look through your code this evening and try to fathom out what exactly each bit is doing.
I do appreciate ALL your help on this.

All the Best.
PAB1

30. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

I hope you are keeping well.
I now have Excel and have run your excellent program.
Unfortunately the results are not quite as expected.
I entered the following combinations to test the program ...

Code:
```1	2	3	4	5	6
1	2	3	7	8	9
3	5	6	7	8	9```
... and they produced the "Covered" results ...

Code:
```2 if 2	33
2 if 3	74
2 if 4	100
2 if 5	44
2 if 6	0
3 if 3	54
3 if 4	110
3 if 5	110
3 if 6	54
4 if 4	44
4 if 5	100
4 if 6	74
5 if 5	18
5 if 6	50
6 if 6	3```
... ( I sorted the "Covered" results above into ascending order of the x in x if y ). The actual results for "Covered" should be ...

Code:
```T if M    Tested Covered         % Not Covered     %
----------------------------------------------------
2 If 2 :      36      33  91,66667       3   8,33333
2 If 3 :      84      84 100,00000       0   0,00000
2 If 4 :     126     126 100,00000       0   0,00000
2 If 5 :     126     126 100,00000       0   0,00000
2 If 6 :      84      84 100,00000       0   0,00000
2 If 7 :      36      36 100,00000       0   0,00000
3 If 3 :      84      54  64,28571      30  35,71429
3 If 4 :     126     126 100,00000       0   0,00000
3 If 5 :     126     126 100,00000       0   0,00000
3 If 6 :      84      84 100,00000       0   0,00000
3 If 7 :      36      36 100,00000       0   0,00000
4 If 4 :     126      44  34,92063      82  65,07937
4 If 5 :     126     114  90,47619      12   9,52381
4 If 6 :      84      84 100,00000       0   0,00000
4 If 7 :      36      36 100,00000       0   0,00000
5 If 5 :     126      18  14,28571     108  85,71429
5 If 6 :      84      53  63,09524      31  36,90476
5 If 7 :      36      36 100,00000       0   0,00000
6 If 7 :      36       9  25,00000      27  75,00000```
The ...

Code:
```2 if 2
3 if 3
4 if 4
5 if 5
6 if 6```
... categories are the only ones that produce the correct "Covered" combinations.
BTW, I love the timer, thats a neat touch.

All the Best.
PAB1

31. ## Re: Calculate Sets and Subsets

I don't quite follow. Could you elaborate?

EDIT: Also, create a log file and see if you can identify any examples of faulty logic. That would help me tremendously.

32. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

The results produced by your program differ from the correct results.

Originally Posted by Ellis Dee
Also, create a log file and see if you can identify any examples of faulty logic. That would help me tremendously.
How would I do this please?.

All the Best.
PAB1

33. ## Re: Calculate Sets and Subsets

The constant at the top of the module needs to be set to True:
Code:
`Private Const UseLogFile = True ' Dump all info to Log.txt as it's generated; turn off to greatly increase speed`

34. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

The Cell "E4" in the sheet named "Statistics" should be the maximum number ( i.e. 9 or whatever ) of ANY ball in ANY of the combinations in the 6 number combinations list. Your code I think, is using it for the total number of combinations to evaluate.
Perhaps we could use something like ...

Code:
`set rng = Worksheets("Data").Range("B3:G" & Range("B3").End(xlDown).Row)`
... to find out how many combinations there are to evaluate and something like a variable called maxVal for the maximum number used in any of the 6 number combinations like ...

Code:
`maxVal = Application.Max(rng)`
... for example.

All the Best.
PAB1

35. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

Originally Posted by Ellis Dee
The constant at the top of the module needs to be set to True:
Code:
`Private Const UseLogFile = True ' Dump all info to Log.txt as it's generated; turn off to greatly increase speed`
Done that.
Where do I get the log file from please.

All the Best.
PAB1

36. ## Re: Calculate Sets and Subsets

Originally Posted by PAB1
Where do I get the log file from please.
It's in the same folder as the spreadsheet.
The Cell "E4" in the sheet named "Statistics" should be the maximum number ( i.e. 9 or whatever ) of ANY ball in ANY of the combinations in the 6 number combinations list. Your code I think, is using it for the total number of combinations to evaluate.
*sigh*

The maximum number of ANY ball in ANY of the combinations is calculated based on all the numbers. You don't enter it; the program figures it out for itself.

The E4 number you're talking about allows you to run the numbers on a subset of the list; if you leave it blank then the program will use the entire list.

37. ## Re: Calculate Sets and Subsets

Note this section of code:
Code:
```    ' Identify largest value by comparing against every value in matrix
For x = 1 To mlngRows
For y = 1 To mlngCols
If varData(x, y) > lngMax Then lngMax = varData(x, y)
Next
Next```

38. ## Re: Calculate Sets and Subsets

Hi Ellis Dee,

Originally Posted by Ellis Dee
The E4 number you're talking about allows you to run the numbers on a subset of the list; if you leave it blank then the program will use the entire list.
If I leave it blank in the sheet named "Statistics" the Cells "B3:E25" in the sheet named "Data" are highlighted.
With the figure 6 in "E3" and "E4" left blank I get the results ...

Code:
```2 if 2	14
2 if 3	32
3 if 3	11
2 if 4	32
3 if 4	27
4 if 4	3```
All the Best.
PAB1

39. ## Re: Calculate Sets and Subsets

Which of those numbers is wrong?

40. ## Re: Calculate Sets and Subsets

Ah, I see that the range object isn't selecting the last cell properly. You'll have to specify the number of rows (E4) and columns (E3) manually every time.

The results then become:
Code:
```2 if 2	33
2 if 3	74
3 if 3	54
2 if 4	100
3 if 4	110
4 if 4	44
2 if 5	44
3 if 5	110
4 if 5	100
5 if 5	18
2 if 6	0
3 if 6	54
4 if 6	74
5 if 6	50
6 if 6	3```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•