Excel VBA - Right Digits Only

Have a request.

Is it possible to create VBA code that will only list all possible sets using the 2nd digits (positions 1 through 5) for a 5/39 game?

Example:
10 - 11 - 12 -13 - 14

Result:
0 - 1 - 2 - 3 - 4 - 5

Thanks!!
 

Frank

Member
Have a request.

Is it possible to create VBA code that will only list all possible sets using the 2nd digits (positions 1 through 5) for a 5/39 game?

Example:
10 - 11 - 12 -13 - 14

Result:
0 - 1 - 2 - 3 - 4 - 5

Thanks!!

Yes it is but you need to aware that theres a lot of duplicated combinations of second digits. Also the order that the second digits appear left to right is not necessarily in ascending order. e.g 19,20,25,30,37 would be 9,0,5,0,7. The combination 9,10,15,20,27 shares the same second digits. When listed top to bottom in a table they are in Lex order, not sorted order, unless you later sort them. There are 575757 combinations in a 5/39 lottery and the macro first lists these 575757 'second digit' combination strings, (formatted as string) e.g 01936, 23458, one per row, so you need a version of Excel with sufficient rows to take them, Excel 2007 or later. It then uses Excels built in duplication remover to only list the 97,003 unique second digit sequences in the exact order of their created position.

It takes about 10 minutes to run on my 32 bit version of Excel, it may take longer on yours.

If you need to split the string into columns, Excel's text to columns feature will do that, or a bit of string handling formulae will change the display format to 0 - 1 - 2 - 3 - 4 if thats how you want to see it.

Sub second_digits539()
Dim a, b, c, d, e As Integer
Dim a1, b1, c1, d1, e1 As Integer
Dim count As Long

Columns("A:A").Select

Selection.NumberFormat = "@"
Range("a1").Select

count = 0

For a = 1 To 35
a1 = a Mod 10
For b = a + 1 To 36
b1 = b Mod 10
For c = b + 1 To 37
c1 = c Mod 10
For d = c + 1 To 38
d1 = d Mod 10
For e = d + 1 To 39
e1 = e Mod 10

count = count + 1

ActiveCell.Offset(count, 0) = CStr(a1 & b1 & c1 & d1 & e1)

Next
Next
Next
Next
Next

Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$575757").RemoveDuplicates Columns:=1, Header:= _
xlNo
Range("A1").Select
End Sub


I hope this does what you require.
 
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!!
 

Frank

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

Sidebar

Top