How To Create Random Lines Of Numbers For Lotto Research Using Excel And VBA

Because lotto draws are random, you can create your own data set to test theories and ideas. You can use the data to identify trends and unlikely combinations, and then run more data to test any theory you might come up with.

What Should Be In Your Lotto Data Set?

Each line of numbers should contain unique random numbers between 1 and 40; you can easily change the code for a different lottery type. Also, it’s a good idea to sort each line numerically, so that when you scan the numbers visually it’s easier to pick up patterns and combinations like too many prime numbers or even numbers.

Let’s begin with the code for random number generation.

Generating Six Random Numbers

The code for random numbers is straightforward:

x = CLng(39 * Rnd() + 1)

The rnd() function gives a decimal between 0 and 1 and the rest of the code converts the result to a whole number between 1 and 40.

The problem is that we need 6 numbers for one line and numbers must not be repeated. There are a few ways to do this, but I’ve opted for the dictionary method as it may be a little more flexible for later work we might do with the data.

'create the dictionary

Set dictTemp = CreateObject("Scripting.Dictionary")

'find 6 unique numbers
for i=1 to 6
x = CLng(39 * Rnd() + 1)

' if the number has been added already then find another number
' else add the number and exit the loop
If Not dictTemp.exists(x) then

dictTemp.Add x, x
Exit Do
End If



For y = 1 To dictTemp.Count
Debug.Print dictTemp(y);

The output should be a list of 6 unique random numbers. To generate more lines of numbers you can simply add an outer loop, like this:

for y=1 to 100


The other issue is to sort the numbers, which is useful to check the code is working and to visually identify trends.

Sorting The Random Lotto Numbers

Sorting anything in VBA is a little complex, but the good news is you don’t need to understand it, just apply the code to your own module. The only thing to beware of is that dictionary objects are not well set up for sorting.

We add an item to a dictionary with this syntax:

dictTemp.add key,item

' example
dictTemp.add 23,23

We can only search by the key for a unique number so we make the key and item the same. To sort, we need to loop through the dictionary in order and then make changes which won’t work in the current format.

I opted to create a temporary dictionary to hold the numbers and then sort them. Like this:

x = 1

For Each e In dictTemp.keys
dictLine.Add x, e
x = x + 1

Now we have the line of numbers in a format we can sort.

dictLine 1,24
dictLine 2,5
dictLine 3,14

Now we apply a sort algorithm to the dictionary.

For i = 1 To dictLine.Count

' take each no. in dictLine
' and compare it to other members

For j = i + 1 To dictLine.Count
If dictLine(i) > dictLine(j) Then
' if larger then save current i in variable
' and swap places

temp = dictLine(i)
dictLine(i) = dictLine(j)
dictLine(j) = temp

End If
Next j

Next i

When I ran the code and asked for 5 lines of 6 random numbers I got the following output:

5 9 23 27 30 33

8 11 13 19 32 34
7 12 17 25 26 28
1 4 6 19 28 29
5 13 14 26 32 38


This article has shown how you can create your own random numbers to use as a base for lotto results trending analysis. You can now apply your own theories to determine patterns and trends.