Nice work Frank.
Wanted to ask how would you code this for a 5/70 game knowing that the limit of rows is at 1,048,576?
Could the additional calculations be place into adjacant columns?
I think there are only 100,000 possible 2nd digit sets for this matrix.
I guess I would need to manually remove duplicates from each of the columns.
My other question is creating 1st digit sets using VBA.
Thanks for all your help!!
For any set of second digits ranging from 0 to 9 in 5 columns
without constraints you can create 100,000 unique combinations.
Well I did ask myself why you would want to see 97000 or so combinations of second digits, what possible use could they be, unless you were to print them out and wallpaper your walls with them ?
Then you extended it to a 5/70 requiring a lot more work, to be absolutely sure that there are the full 100,000 as you suggest. Common sense suggests that if 0 to 9 were allowable in every one of the 5 positions of a 5/70 then there will be 10 to the power 5 combinations of second digits = 100,000.
To save you a job I can tell you that the 5/39 where you have four decades spread over 5 columns, there are not enough degrees of freedom to choose a set of firsts digits to suit any combination of second digits. Thats why there are about 3000 impossible combinations of digits 0 to 9 in a 5/39.
In the 5/70 there is a bigger choice of first digits to spread across the 5 columns so you
can create any genuine combination from the theoretical 100,000 sets of second digits.
Yes you could code your PC to prove this but Is the work worth the end product ? To use my my method you can't generate 12,103,014 combinations and write them to a spreadsheet with about a million rows available.
I dont know if you you know how to use VBA's Dictionary, if so you could write all the combinations of second digits into that and it would count the duplicates of each one, leaving a list that could be written to a column of a spreadsheet, and it would write out 100,000 of them. I'm not really into the dictionary, so I'm not going there.
The hard way is to modify the nested loops so that they generate the combinations
in instalments, e.g
for a =
1 to 1
for b= a+1 to 67
for c= b+1 to 68
for d=c+1 to 69
for e= d+1 to 70
after running produces
the first 10,000 unique (duplication checked ) combinations of second digits from 864501 rows of combinations in column A.
You'd need to store these away from column A, leaving it empty. Run the macro multiple times but
keep modifying
a 'from' and 'to' according to this table, until all 12,103,014 combinations had been run, duplication checked (in each column only). You end up with 18 columns of combinations you have carefully stored elsewhere. However, these 18 columns need to be appended to each other and duplication checked
again manually till you end up with just one column of 100,000 unique combinations. But you are not going to do that, because you don't need to.
I've already done it.
a | | | |
from | to | | uses # rows |
1 | 1 | | 864501 |
2 | 2 | | 814385 |
3 | 3 | | 766480 |
4 | 4 | | 720720 |
5 | 5 | | 677040 |
6 | 6 | | 635376 |
7 | 7 | | 595665 |
8 | 8 | | 557845 |
9 | 9 | | 521855 |
10 | 10 | | 487635 |
11 | 11 | | 455126 |
12 | 12 | | 424270 |
13 | 14 | | 762300 |
15 | 16 | | 657306 |
17 | 19 | | 813450 |
20 | 23 | | 815121 |
24 | 30 | | 875931 |
31 | 70 | | 658008 |
So you dont need to see 100,000 combinations.
Just think up any 5 second digits from 00000 to 99999. They will be correct.
Well to answer your second question, if you want to code for
first digits in a 5/39 you only need to modify the macro by changing all statements that use statements like a1= a Mod 10 , b1=b mod 10, etc to:-
a1 = Int(a / 10),
b1=Int(b / 10), etc.
You should get 56 combinations. ( you need to clear column A first though).
Thats me done, Im going for a lie down.