# I need Excel expertise from great GillesD!

#### lottomaniac649

##### Member
Hello Excel guru GillesD!

You may be knowing that I'm in the process of making a spreadsheet with macros to predict the betline for a specified lottogame (i.e. for 6/49 game) and the poor performance of my predictions so far. Now, I've resorted with a indepth analysis to predict the individual betlines. As I find the randamize method takes too long time to correctly spit out the right combination out of 14 billion odds, I've decided to include the combinations itself in a two columns with a split of oddnumbers (4 numbers) evennumber (2 numbers). I've tried to hold the data in a multidimensional array and inputing the variables (all 6 numbers - one combination) for the checking in a particular range (i.e. a2:f2) but I've not successful. All but this module alone need a fix from your expertise. For clarity, I give the following details.
1. Column 'B2' to 'D12651" contain four numbers (all odd) - 12650 lines
2. Column 'E2' to 'F277' contain two number (all even) - 276
3. Column 'A' to 'F" ..with blank to hold the combin generated from above two (i.e. 4 numbrs from 'B22' & 2 numbers from 'E2 to F2')
4. After inputting the combination one by one in the input range (\$A\$2:\$F\$2), I've another macro to either pick the combination or drop. This macro will restore the range (\$A\$2:\$F\$2) as empty to hold the result of next combination by loop from the array.

I believe that I've communicated my requirement.

Thank you for your time & expertise.
Regards.
LM649

#### GillesD

##### Member
Getting combinations

Lottomaniac

I am not too sure I understand what you want. As I see it, you want to generate all possible combinations from 12,650 lines of 4 odd numbers with 276 lines of two even numbers. That gives 3,491,400 combinations altogether and a macro can easily generate those.

But what I do not understand is what you want to do with those combinations (accept or reject them based on a certain criteria?) and then what is to be done with those you decide to keep. Why overwrite data in columns A to F?

#### lottomaniac649

##### Member

Hello GillesD!

Thank you very much for your immediate favourable reply. I want only the selected combinations from all 3,491,400 combinations for a 4 numbers odd and 2 numbers even combination of lotto649 result. I've another macro to handle with the criteria to keep the selected betline from the result of the array which holding all the combinations and spitting one by one in the absolute range "A1:F1". What I tried is given below for you reference. Hope, you'd fixt it!.
Thanks for your efforts and time.
Best Regards.
LM649

Option Base 1
Sub oddevenpick6()
Dim odd As Integer
Dim even As Integer
Dim i As Variant
Dim j As Variant
Dim cell As Range
Application.ScreenUpdating = False
Sheets("trigger").Select
odd = [D3000:G15649]
even = [H3000:I3275]
i = 1
j = 1
Do While i >= LBound(odd) And i <= UBound(odd)
For Each cell In Range("D261:G261")
cell.Formula = odd(i)
Next
Do While j >= LBound(odd) And j <= UBound(odd)
For Each cell In Range("H261:I261")
cell.Formula = even(j)
Next
Range("\$BO\$1").Select
If ActiveCell.Value = "DROP" Then Call numdelete
If ActiveCell.Value = "COPY" Then Call copynumber
j = j + 1
i = i + 1
Loop
For j = LBound(even) To UBound(even)
Next
Loop
For i = LBound(odd) To UBound(odd)
Next
End Sub

#### GillesD

##### Member
Macro for 4 odd and 2 even numbers

The following macro will generate all combinations of the 12,650 possibilities of 4 numbers (starting in cells A2 to D2 and down) with 276 combination of 2 numbers (starting in E2 to F2 and down).

The values of six numbers are placed in variables N1 to N6 (but you could place them into an array). It is up to you then to do whatever you want with this combination in a macro that can accept or reject them, store the values where you want, etc. Call your macro where indicated.

Option Explicit
Dim nEven(276, 2) As Integer
Dim I As Integer, J As Integer
Dim N1 As Integer, N2 As Integer, N3 As Integer
Dim N4 As Integer, N5 As Integer, N6 As Integer

Sub Combin4x2()
Application.ScreenUpdating = False
' Place even numbers in the array nEven(276,2)
Range("E1").Select
For I = 1 To 276
nEven(I, 1) = ActiveCell.Offset(I, 0).Value
nEven(I, 2) = ActiveCell.Offset(I, 1).Value
Next I
' Generate all combination 4 odd and 2 even numbers
Range("A1").Select
For I = 1 To 12650
For J = 1 To 276
N1 = ActiveCell.Offset(I, 0).Value
N2 = ActiveCell.Offset(I, 1).Value
N3 = ActiveCell.Offset(I, 2).Value
N4 = ActiveCell.Offset(I, 3).Value
N5 = nEven(J, 1)
N6 = nEven(J, 2)
' Variables N1 to N6 now contain 6 numbers for a combination
' Call your macro from this place
Next J
Next I
Application.ScreenUpdating = True
End Sub

#### lottomaniac649

##### Member
Hello GillesD!
Thanks a lot for your great work!. I believe it'll work fine. Just I've seen your posting and replying. As the file is locked in my office computer, I'll test it and give a feedback. I don't think it'll require as I consider you as a genius in Excel. You know to be genius, one requires 1 percent inspiration and 99 percent perciperation. I've got 1 percent from you and I've to accumulate rest from me.
Thanks a lot again!
With Best Regards.
LM649

P.S. This snag has been found out by me in the last week's sikkim super lotto draw (my favourtie game) as my code exactly matched with the result code and the combination was missing. On a thorough digging, I found out that the randomize method didn't pop-up the combination. Then I tried putting that particular 'code' intact and allowing my macro to take one full day. Alas! it didn't give that combination. So, with randamize we can't get whatever combination we want.

#### lottomaniac649

##### Member

Hello GillesD!

I've put your code in entirity with the other codes what I've been using for copying and deleting. When I run the macro it goes on without producing any results and I think I've done some messy things in the code but I couln't rightly point out or correct. Can u please advise me what code I've to correct.
The code:
Code:
``````Option Explicit
Dim nEven(276, 2) As Integer
Dim I As Integer, J As Integer
Dim N1 As Integer, N2 As Integer, N3 As Integer
Dim N4 As Integer, N5 As Integer, N6 As Integer

Sub Combin4x2()
Application.ScreenUpdating = False
' Place even numbers in the array nEven(276,2)
Range("E3000").Select
For I = 1 To 276
nEven(I, 1) = ActiveCell.Offset(I, 0).Value
nEven(I, 2) = ActiveCell.Offset(I, 1).Value
Next I
' Generate all combination 4 odd and 2 even numbers
Range("D3000").Select
For I = 1 To 12650
For J = 1 To 276
N1 = ActiveCell.Offset(I, 0).Value
N2 = ActiveCell.Offset(I, 1).Value
N3 = ActiveCell.Offset(I, 2).Value
N4 = ActiveCell.Offset(I, 3).Value
N5 = nEven(J, 0)
N6 = nEven(J, 1)
' Variables N1 to N6 now contain 6 numbers for a combination
' Call your macro from this place
Range("D261").Select
Range("D261").Value = N1
Range("E261").Select
Range("E261").Value = N2
Range("F261").Select
Range("F261").Value = N3
Range("G261").Select
Range("G261").Value = N4
Range("H261").Select
Range("H261").Value = N5
Range("I261").Select
Range("I261").Value = N6
Range("\$BO\$1").Select
If ActiveCell.Value = "DROP" Then Call numdelete
If ActiveCell.Value = "COPY" Then Call copynumber
Next J
Next I
Application.ScreenUpdating = True
End Sub``````
Thanks & Regards.
Bye
LM649

#### GillesD

##### Member
Macro problems

Lottomaniac

A - First comment
In generating combinations, by using Nx=Activecell.Offset... command, I do not need to change the position of the active cell to determine the value I want. In my macro, that position is A1 and in yours, it is D3000. But then, you select other cells to input values N1 to N6 on line 261. So the macro loses its postion to recover new data after the first pass. This can be corrected by placing the command Range("D3000").Select just above the Next J near the end of the macro.

B - Second comment
My macro assumes that the odd numbers are in columns A, B, C and D and the even numbers in E and F and that is why I position the active cell in A1. But in your case, with data starting at line 3000, you have to change the references to find the right numbers (odd numbers in A-B-C-D and even numbers in E-F) or the offset value for the appropriate column.

C - Third comment
I always use the Option Base 1 command for may arrays and you may want to add this command after the Option Explict at the top and refer the variables nEven(x, 1) and nEven(x, 2) afterward.

#### lottomaniac649

##### Member
Hello GillesD!

Thanks for your advise. Yes. I erred in offsetting the right cells. Now it works fine but it takes enormous time. Is there any scope to reduce the time. And, I'm going to extend this theory to odd5even1, odd3even3, odd2even4 & odd1even5. After finishing everything I'll post the spreadsheet for your expert review.

Thanks a lot again!
Bye
LM649

#### GillesD

##### Member
Slow macro

Lottomaniac

Time is a relative notion. With my initial macro posted here, it took me 2min45s to cycle through all 3,491,400 combinations (12,650 x 276). Of course, this does not take into account any treatment you apply to a combination. Maybe it is what you do with a combination that adds too much time. But I think that my macro works relatively fast (although it could possibly be improved) for what it has to do. Working with 2 arrays, nOdd(12650,4) and nEven(276,2) might be one possibility to look into.

#### GillesD

##### Member
New improved macro

Here is a revised version of my initial macro and you will see a definite improvement in generating all combinations (12,650 x 276) as it works with arrays. Odd numbers are in A2 to D12651 and even numbers in E2 to F277.

Option Explicit
Option Base 1

Dim nOdd(12650, 4) As Integer, nEven(276, 2) As Integer
Dim I As Integer, J As Integer
Dim N1 As Integer, N2 As Integer, N3 As Integer
Dim N4 As Integer, N5 As Integer, N6 As Integer

Sub Comb_4Ox2E()
Application.ScreenUpdating = False
' Place odd numbers in array nOdd(12650,4)
Range("A1").Select
For I = 1 To 12650
nOdd(I, 1) = ActiveCell.Offset(I, 0).Value
nOdd(I, 2) = ActiveCell.Offset(I, 1).Value
nOdd(I, 3) = ActiveCell.Offset(I, 2).Value
nOdd(I, 4) = ActiveCell.Offset(I, 3).Value
Next I
' Place even numbers in array nEven(276,2)
Range("E1").Select
For I = 1 To 276
nEven(I, 1) = ActiveCell.Offset(I, 0).Value
nEven(I, 2) = ActiveCell.Offset(I, 1).Value
Next I
' Generate all combination 4 odd and 2 even numbers
Range("A1").Select
nComb = 0
For I = 1 To 12650
For J = 1 To 276
N1 = nOdd(I, 1)
N2 = nOdd(I, 2)
N3 = nOdd(I, 3)
N4 = nOdd(I, 4)
N5 = nEven(J, 1)
N6 = nEven(J, 2)
' Variables N1 to N6 now contain 6 numbers for a combination
' Call your macro from this place
Next J
Next I
Application.ScreenUpdating = True
End Sub

#### johnph77

##### Member
O/E breakdown for 6/49 matrix:

0O/6E - 134,596.
1O/5E - 1,062,600.
2O/4E - 3,187,800.
3O/3E - 4,655,200.
4O/2E - 3,491,400.
5O/1E - 1,275,120.
6O/1E - 177,100.
Total - 13,983,816.

gl

j

#### lottomaniac649

##### Member
Hello GillesD!

Thanks again for your great enthusiasm in improving the macro and for the helping tendency. I think I did trouble you too much. Sorry!

After running the macro overnight, when I saw this morning, I was wondering that the macro was still running. When I break the run and saw the results, my predicament is totally shattered like anything. Yes. It has generated around 600+ combinations and the macro was looking at the odd array 1..3..33,49. Guess, how many combinations still remain to generate. Here, I concluded that though the macro was doing wonder but with the generation of so many combinations, I don't think anyone individually can hit the jackpot.

Finally, I consider my earlier method using randamize, although randam but it has produced based on the frequency table of the game and will be a one for the practical purposes to win the lotto game which is nothing but pure random!.
Best Regards.
LM649

#### GillesD

##### Member
Speed or lack of it

Lottomaniac

I do not know what you are using to run my macro (maybe a slow computer) but my first macro got through all 3,491,400 combinations under 3 minutes. And with my new improved macro, it took less than 15 seconds.

So please do me a favor. Just run through both of my macros without calling any of your macros and measure the time it takes to complete (no need to be very precise). Putting a STOP command as last command can alert you when it finishes running.

Then do the same exercise but this time call your macros within each of my macros and again time it. And please do post your results here.

And by the way, if any of the 4 macros has not completed its task within 30 min, you can stop it and this will give you an indication where to look for if you want to get results in an acceptable period of time.

#### lottomaniac649

##### Member
Hello GillesD!

You're very correct. When I ran your improved version of macro without my cobmination look up macros, it took neraly half a minute in my office computer and lesser time in my home computer. Your previous version also yielded the result within a minute. As the timing has become immaterial because of lots combination it generates I don't want this to continue. Let me find some more filters to really squeeze the odds.

Thanks a lot again!

With Best Regards.
LM649

#### srestha1968

##### Member
Hello to everybody here!
i am from India and would like to know if any intellect from this forum can provide me with a software in ms excel to find out a single digit (single card) out 12 digit( 12 deck of card) combination lotto. Let me clarify, basically all the face card from Jack to Queen & finally King (12 cards) a single card is picked randomly. The lotto is called Joker and the site is <<< link deleted >>> (opt joker) for details. Thanks in advance if any intellect from this forum can solve my solution. My email is <<< emails are not allowed >>>
thanks every buddy
Srestha

Last edited by a moderator:

#### Blizz

##### Member
Hello guys,

As I could see from earlier posts, analysing drawings via excel have many disavantages. Very slow data processing, vb programming (ouch) , not very portable to other lotteries without major macro rewrites, hard to understand sheet data for inexperienced users, a single programming error for one cell could lead to have the entire worksheet completely false, etc...

I've been working on a similar application since the last two years but I didn't started the coding yet, I just finished the analysis part of it. As the months did go by, I have found that the same pieces of code would work with every type of lottery available today. The beauty of the system is that it will adjust its strategies according to its own predictions vs the past drawings. I cannot give all the details cause I would spend an entire hour explaing it but I can respond to questions if any. It would be written in delphi and by using memory tables would dramatically reduces the processing time needed for analysis. What takes 15 mins in excel would take about 15 secs with object pascal.

Since I can't test all lotteries myself, I will test it for keno at first and then if some users wants to test it with their own lottery, I would be willing to give them a copy for testing purposes. It will probably be a commercial application in the future and every person who will participate in his developement ie: testing, ideas, etc... will get the software and lifetime updates.

So stay tune, I'll begin coding my work this week-end.

Regards.

Blizz

#### variablinda369

##### Member
would be better a code that you are able to choose -how many even/odd you want in the output, the minimum and maximum sum with exception, the range mi/max with exc. input the history winning list/compare with the list generate by the code and find duplicate in the new list and eliminate. one of you can do that, I am working in something like that but I need help.

#### variablinda369

##### Member
vba code with filters and userform excel 2003

HELLO GILLESD AND LOTTOMANIAC649, easy to guess you are programmers,

#### variablinda369

##### Member
books

lottomaniac649 said:
Hello GillesD!

Thank you very much for your immediate favourable reply. I want only the selected combinations from all 3,491,400 combinations for a 4 numbers odd and 2 numbers even combination of lotto649 result. I've another macro to handle with the criteria to keep the selected betline from the result of the array which holding all the combinations and spitting one by one in the absolute range "A1:F1". What I tried is given below for you reference. Hope, you'd fixt it!.
Thanks for your efforts and time.
Best Regards.
LM649

Option Base 1
Sub oddevenpick6()
Dim odd As Integer
Dim even As Integer
Dim i As Variant
Dim j As Variant
Dim cell As Range
Application.ScreenUpdating = False
Sheets("trigger").Select
odd = [D3000:G15649]
even = [H3000:I3275]
i = 1
j = 1
Do While i >= LBound(odd) And i <= UBound(odd)
For Each cell In Range("D261:G261")
cell.Formula = odd(i)
Next
Do While j >= LBound(odd) And j <= UBound(odd)
For Each cell In Range("H261:I261")
cell.Formula = even(j)
Next
Range("\$BO\$1").Select
If ActiveCell.Value = "DROP" Then Call numdelete
If ActiveCell.Value = "COPY" Then Call copynumber
j = j + 1
i = i + 1
Loop
For j = LBound(even) To UBound(even)
Next
Loop
For i = LBound(odd) To UBound(odd)
Next
End Sub
any book can teach me how to do this codes

#### PAB

##### Member
Hi variablinda369,

If you tell me how many ODD & EVEN numbers you want in each combination and what Lotto you are playing I will upload an Excel file with ALL the combinations for you.
Can you also tell me what version of Excel you are using as this has an impact on the number of rows and columns available to you.
The Excel file will of course be quite big so I will have to check what the maximum file size that can be uploaded is first.

Regards,
PAB

-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
Mathematics is the language of nature.
Everything around us can be represented and understood through numbers.
If you graph the numbers of any system, patterns emerge. Therefore, there are patterns everywhere in nature.