Calculate Sets and Subsets

[PAB1]

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

[Hack]

Excel VBA question moved to Office Development

[Ellis Dee]

I don't follow.

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

[PAB1]

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

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

[Ellis Dee]

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?

[Ellis Dee]

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.

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

[PAB1]

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

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.
Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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?

[PAB1]

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

[GISMillenium]

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

[Ellis Dee]

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.

[Ellis Dee]

Oops, sorry, thought you were the OP.

[PAB1]

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

[PAB1]

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.

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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

[PAB1]

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

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

To answer your question, the first step is as follows.
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 :-

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.

Thanks in Advance.
All the Best.
PAB1

[PAB1]

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

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

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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.

[PAB1]

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.

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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

[Ellis Dee]

I started messing with this and am making decent progress. I notice a contradiction in a couple of your posts: (bolding mine)

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

[PAB1]

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

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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.

[Ellis Dee]

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.

[Ellis Dee]

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.

[Ellis Dee]

Module-level declarations:

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 Long
Private mlngCols As Long
Private mlngOutputRow As Long
Private mlngOutputColumn As Long
 
Private mstrLogFile As String ' Log file path & filename
Private FileNumber As Long ' File handle for log file

Main program entry point:

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

' Return column letter given a column number
Private 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 development
Private 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 If
End 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

[Ellis Dee]

Generate combinations:

' Combination generator engine
' plngMax: Highest number from matrix
' plngNumbers: Number of elements in each combination
Private 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 lngNum
End Sub

Compare the combinations against the set of numbers from the data sheet:

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, lngFound
End Sub
 
' Find matches for a given data row, combination and bucket size
Private 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 If
End Function

[Ellis Dee]

Let me know if you have any questions.

[PAB1]

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.

Thanks in Advance.
All the Best.
PAB1

[PAB1]

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

1	2	3	4	5	6
1	2	3	7	8	9
3	5	6	7	8	9

... and they produced the "Covered" results ...

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

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

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.

Thanks VERY much in Advance.
All the Best.
PAB1

[Ellis Dee]

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.

[PAB1]

Hi Ellis Dee,

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

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

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

The constant at the top of the module needs to be set to True:

Private Const UseLogFile = True ' Dump all info to Log.txt as it's generated; turn off to greatly increase speed

[PAB1]

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

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

maxVal = Application.Max(rng)

... for example.

Thanks in Advance.
All the Best.
PAB1

[PAB1]

Hi Ellis Dee,

The constant at the top of the module needs to be set to True:

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.

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

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.

[Ellis Dee]

Note this section of 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

[PAB1]

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

2 if 2	14
2 if 3	32
3 if 3	11
2 if 4	32
3 if 4	27
4 if 4	3

Thanks in Advance.
All the Best.
PAB1

[Ellis Dee]

Which of those numbers is wrong?

[Ellis Dee]

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:

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