If ... Then Loop

[Paul Black]

Hello Everybody,

I am Basically trying to Find Out the Combinations Covered in a List of
Combinations.
I have a Set of 6 Number Combinations in Cells "G13:L27" ( the Number
of Combinations could be More or Less ).
In this Example I am Using a Wheel with 24 Numbers :-

1,3,7,12,15,16
1,4,5,17,20,21
1,8,9,10,19,22
1,13,14,18,23,24
2,3,6,9,21,23
2,10,12,14,16,20
2,11,15,19,20,24
3,4,7,10,18,24
3,5,7,14,17,19
4,6,8,14,15,22
4,9,11,13,16,19
5,10,13,15,17,23
5,11,12,18,21,22
6,8,12,16,17,24
7,8,13,20,22,23

Here is the Code Somebody Kindly Provided which Cycles through ALL
Combinations ( 6 Numbers from 24 Numbers in this Case Produces 134,596
Combinations ) and Compares ALL the Combinations with ALL the
Combinations in the Above Wheel. The Below Code Finds the Coverage of 5
Numbers if 5 Numbers Matched ...

Sub test_5()
Dim a, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
a = Range("g13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub

.... and Produces the Correct Result of 90.
How can the Code be Modified ( Or Improved ) to Also Produce the
Combinations Covered for the Categories ...

Matched Covered
2 if 5 = 42,504
3 if 5 = 35,720
4 if 5 = 4,140
5 if 5 = 90 ( the Code Already Provides this Result )

.... Please.

I Hope I have Explained this Clear Enough.
Many Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

I Don't know if this Helps with the Overall Explanation or Not.
I was Told for the Interpretation of the 3 if 5 Category that you Need
to Cycle through ALL 5 Number Combinations that can be Constructed from
the Total Numbers Used in the Wheel ( 24 in this Case ). So if the
Wheel Contains "x" Unique Numbers, you Need to Cycle through ALL 5
Number Combinations from those "x" Numbers. Then you Need to Scan the
Wheel for Each 5 Number Combination Produced and Compare it with Each
Line in the Wheel to see if that Line Matches the 5 Number Combination
in *EXACTLY* 3 Numbers. If it does, then that Combination of 3 if 5 is
Covered and Added to the Total and there is NO Need to Continue to
Check for that Particular Combination Any Further. You then go onto the
Next Combination to Check and so on Until ALL Combinations have been
Cycled through and Checked with the Wheel.

Thanks in Advance.
All the Best.
Paul

 

[Tom Ogilvy]

was Told . . .

Your acting like you have never asked for anthing like this before?

Produces:
13983816
0 Matches (no bonus): 6096454
1 Matches (no bonus): 5775588
2 Matches (no bonus): 1851150
3 Matches (no bonus): 246820
4 Matches (no bonus): 13545
5 Matches (no bonus): 252
6 Matches (With bonus): 6
6 Matches (no bonus): 1
At least 3 matches 260624
--

Regards,

Tom Ogilvy

 

[Paul Black]

Hi Tom,

You are Quite Right, you have Previously Provided me with Excellent
Code that Produces the Exact Results that you have Posted. That Code
Cycled through ALL Combinations in a 649 Lotto and Produce the List of
ALL Possible Winning Combinations in Each Category.
This Request is Different Because it is Based on a List of Set Numbers
and Involves Different Criteria.

Thanks in Advance.
All the Best.
Paul

 

[Tom Ogilvy]

Here is the Code Somebody Kindly Provided which Cycles through ALL

Combinations ( 6 Numbers from 24 Numbers in this Case Produces 134,596
Combinations ) and Compares ALL the Combinations with ALL the
Combinations in the Above Wheel. The Below Code Finds the Coverage of 5
Numbers if 5 Numbers Matched ...

The code you show doesn't do what you say. It generates 90 numbers (the 6
combinations per row) and counts those that are unique. It only loops 90
times. There is no comparison to 134,596 numbers, nor does it generate
those numbers.

 

[Paul Black]

Thanks for the Reply and Information Tom.
Is there any way that the Code can be adapted to accomodate the
criteria I specified at all Please.
Any help will be GREATLY appreciated.

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Again,

I have Tried Adapting the Code but to NO Avail.
I keep getting the ERROR Message ...

This Key is Already Associated with an Element of this Collection.

.... when I Compile & Run it.
Can the Code be Written Without Using ...

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary")

.... as this is the Problem I think.
To be Honest, I have NO Idea what this Piece of Code Actually does, but
it Seems to Stop Any Additional Requirements from being Executed.

I have been trying to get an Answer to this for about Six Months.
Any Help or Suggestions will be GREATLY Appreciated.

Thanks in Advance.
All the Best.
Paul

 

[Paul Black]

Hi Everyone,

I will try to Explain it a bit Clearer.
Lets Assume we have a Six Number Combination of 1,2,3,4,5,6.
There are 6 Combinations of 5 Numbers from 6, they are as Follows :-
Combination 1 = 1,2,3,4,5
Combination 2 = 1,2,3,4,6
Combination 3 = 1,2,3,5,6
Combination 4 = 1,2,4,5,6
Combination 5 = 1,3,4,5,6
Combination 6 = 2,3,4,5,6

Now if we just Concentrate on the Fact that we are Lucky Enough to have
5 of the 6 Numbers Drawn, Numbers 1,2,3,4,5 for Example.
There are 5 Combinations of 4 Numbers from 5, they are as Follows :-
Combination 1 = 1,2,3,4
Combination 2 = 1,2,3,5
Combination 3 = 1,2,4,5
Combination 4 = 1,3,4,5
Combination 5 = 2,3,4,5

There are 10 Combinations of 3 Numbers from 5, they are as Follows :-
Combination 1 = 1,2,3
Combination 2 = 1,2,4
Combination 3 = 1,2,5
Combination 4 = 1,3,4
Combination 5 = 1,3,5
Combination 6 = 1,4,5
Combination 7 = 2,3,4
Combination 8 = 2,3,5
Combination 9 = 2,4,5
Combination 10 = 3,4,5

There are 10 Combinations of 2 Numbers from 5, they are as Follows :-
Combination 1 = 1,2
Combination 2 = 1,3
Combination 3 = 1,4
Combination 4 = 1,5
Combination 5 = 2,3
Combination 6 = 2,4
Combination 7 = 2,5
Combination 8 = 3,4
Combination 9 = 3,5
Combination 10 = 4,5

As I said Previously :-

I was Told for the Interpretation of the 3 if 5 Category that you Need
to Cycle through ALL 5 Number Combinations that can be Constructed from
the Total Numbers Used in the Wheel ( 24 in this Case ). So if the
Wheel Contains "x" Unique Numbers, you Need to Cycle through ALL 5
Number Combinations from those "x" Numbers. Then you Need to Scan the
Wheel for Each 5 Number Combination Produced and Compare it with Each
Line in the Wheel to see if that Line Matches the 5 Number Combination
in *EXACTLY* 3 Numbers. If it does, then that Combination of 3 if 5 is
Covered and Added to the Total and there is NO Need to Continue to
Check for that Particular Combination Any Further. You then go onto the
Next Combination to Check and so on Until ALL Combinations have been
Cycled through and Checked with the Wheel.

I Hope this makes it a Bit Clearer what I am Trying to Achieve.
I Appreciate your Time & Effort with this.
All the Best.
Paul

 

[Paul Black]

I will Try and Explain this Clearly One Last Time.
This is what I have been told.

The maths is straightforward to do this. We have a wheel C(n,k,t,m)=b
where ...

*n=the total balls we want to wheel
*k=the ticket size (e.g. a 6 ball game has k=6)
*t=the prize division we want to guarantee a win
*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.
*b=the total tickets required to play.

Now, if we are interested to find the 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 tickets of your wheel (each ticket
contains k numbers).
A combination of those A is covered if there is at least one ticket in
your wheel, 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 if it
contains at least x numbers in common with at least one ticket of your
wheel. If it does, then it is covered.
You produce all combinations (not numbers) for the x if y category and
test each such combination (contains y numbers) if it is covered by at
least one combination in your wheel (k numbers). You don't test against
all numbers in the wheel. A combination of "x if y" is covered if there
is at least one ticket in your wheel that has in common t numbers.

e.g. for a simple wheel C(5,4,3,3)=4 ...

1) 1 2 3 4
2) 1 2 3 5
3) 1 2 4 5
4) 1 3 4 5

.... we want to test the 2 if 3 (to be covered), nCk(5,3)=10
combinations ...

1 2 3
1 2 4
1 2 5
1 3 4
1 3 5
1 4 5
2 3 4
2 3 5
2 4 5
3 4 5

We go through all 10 combinations to find the coverage. E.g. we test 1
2 3 with our wheel. We can see that 1) & 2) & 3) & 4) tickets cover
this combination as they contain at least t=2 numbers in common, thus 1
2 3 is covered. We need only one ticket in the wheel to cover our
combination. If you test the above 10 combinations, all of them are
covered, thus the C(5,4,3,3)=4 has 2if3=100% coverage.

The algorithm in plain English (create array, iterate this cycle,
if-else etc)

code:--------------------------------------------------------------------------------
declare partial_results as structure of
min = array of numbers[ 4 ]; //min[0] is match in six numbers,
min[1] is match in five numbers, etc
max = array of numbers[ 4 ];
comboCount as number;
end declare

function main
declare final_results as list;

for each c=1,2,3,4,5,6 to 44,45,46,47,48,49 do
results = new partial_results;
for each d=(tickets being tested) do
match = how_many_numbers_match( c, d );
increment( results, match );
end for
similar = find_similar_results( final_results, results );
if similar is NULL then
add results to final_results;
else
add( results, similar );
end if
end for

//now the final_results list contains all data for the coverage
report
//the following applies to each item in this list:
//"comboCount tested combinations produce min[0] to max[0] of
jackpot hits,
//and min[1] to max[1] of 'match 5' hits and min[2] to max[2] of
'match 4' hits
//and min[3] to max[3] of 'match 3' hits
end function

function increment( res as partial_results, match as number )
index_to_increment = 4 - (match-2); //reverse the match index to
have highest match at index '0'
min[ index_to_increment ] = min[ index_to_increment ] + 1;
max[ index_to_increment ] = max[ index_to_increment ] + 1;
comboCount = comboCount+1;
end function

function add( res1 as partial_results, res2 as partial_results )
res2.comboCount = res2.comboCount + res1.comboCount;
for i=0 to 3 do
if res1.min[ i ] < res2.min[ i ] then
res2.min[ i ] = res1.min[ i ];
end if
if res1.max[ i ] > res2.max[ i ] then
res2.max[ i ] = res1.max[ i ];
end if
end for
end function

function find_similar_results( list, results )
for each r=partial_results from the list do
for i=0 to 3 do
if results.max[ i ] > 0 then
if results.max = r.max[ i ] then
return r;
else
break;
end if
else if r.max[ i ] > 0 then
break;
end if
end for
end for
return NULL;
end function

The algorithm calculates the coverage on assumption that you hit 6 from
your wheel. if you want to see coverage report for hit 5 or hit 4 only
then the outermost loop must be shortened to ...

for each c=1,2,3,4,5 to 45,46,47,48,49 do
or
for each c=1,2,3,4 to 46,47,48,49 do

.... respectively.

Any Help will be Appreciated.
All the Best.
Paul