How to display combinations from a data set so i can then filter ?

[me2012]

So my mates and i would love to have some numbers to play with on keno while having a beer , and maybe win occasionally

I have searched the net and this forum , but im still stuck. Closest being this

http://www.mrexcel.com/forum/excel-questions/626608-lottery-results-check-please-help.html

** What i need is a formula/s (VBA & macros r beyond me) to display how many times a combination set of numbers has occurred from a data set.
Starting from 1 to 8,145,060 (6 from 45) combination's and then to check each line to the drawn numbers (data set) to find out hot/cold atm sets.

I know how to use the filters so im ok there (to sort out what i want) , and yes i know keno is an 80 number game, but I just ignore the last 35 numbers to give me better odds.

What i have so far :

I'm currently using Excel 2007

Each Sheet ( 1 to 9 ) has 1,000,000 combinations (please see pic below) ** ignore Column H for the moment its not what i need **

And Sheet 10 has the Draw History ( data set ) please see pic below ** Currently it has 16,000 draws (2 months worth) i will MAX it out at 20,000.

What i need help with :

1. I need column H on each of the nine sheets to display how many times , 4 of the numbers in that lines combination have occurred from the data set in sheet 10.
2. I need column I on each of the nine sheets to display how many times , 5 of the numbers in that lines combination have occurred from the data set in sheet 10.
3. I need column J on each of the nine sheets to display how many times , 6 of the numbers in that lines combination have occurred from the data set in sheet 10.

I can then use filters for the info im after

I have no idea how to go about this and any help/pointers in the right direction to achieve this would be greatly appreciated ( free beers )

Thank You for Reading.

ps :

If anyone wants the workbook (.xlsx) to help me or try their own system i have uploaded here (152mb .rar) :

http://ul.to/75ckwp3i

Original Site where i got the Combinations :

https://sites.google.com/site/australianlottostatistic/anouncements/whatsnew

 

[oldbrewer]

[TABLE="width: 558"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]PICK[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]24[/TD]
[TD]33[/TD]
[TD]45[/TD]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]winners[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WINNING NUMBERS[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]FORMULA IN MARKED CELL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]IF(ISERROR(MATCH(B1,$L$1:$Q$1,0)),0,1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

YOU CAN MAKE YOUR OWN HISTORY NOW AND TRY AND USE IT TO BEAT PURE CHANCE - YOU WILL fail

 

[me2012]

[TABLE="width: 558"]
<tbody>[TR]
[TD]PICK[/TD]
[TD]2[/TD]
[TD]7[/TD]
[TD]24[/TD]
[TD]33[/TD]
[TD]45[/TD]
[TD]49[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]winners[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]46[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WINNING NUMBERS[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]^[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]FORMULA IN MARKED CELL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 8"]IF(ISERROR(MATCH(B1,$L$1:$Q$1,0)),0,1)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

@oldbrewer Thanks for replying , but i cant get that Formula to work with my criteria :

1. I need column H on each of the nine sheets to display how many times (Total) , 4 of the numbers in that lines combination have occurred from the data set in sheet 10.
2. I need column I on each of the nine sheets to display how many times (Total) , 5 of the numbers in that lines combination have occurred from the data set in sheet 10.
3. I need column J on each of the nine sheets to display how many times (Total) , 6 of the numbers in that lines combination have occurred from the data set in sheet 10.

because after this i would like to know on average how many draws since these events last occurred, maybe i need more than just a formula to achieve this.

-------- A ------ -B -C- D- E- F- G - --H - I --- J ------ K (Average draws for J )
eg :---Set 1 --- 1- 2 -3 - 4 -5 - 6 --- 150 -90 -33 --- 690

And Yes its pure chance especially with a mechanical lotto system, but Keno is purely electronic using a pseudo random number generator with a static seed ( that they cant change by law) so cycles in number combinations will be easier to predict with enough data.

 

[oldbrewer]

the example I gave was for one "try" - If you had a thousand tries you could SUM the number of 1's in each row and use countif sum=4, 5 and 6

enough data = billions and Excel ain't big enough.......

try it - BUT to generate your "tries" you will need a random number generator - I used one once that generated a random seed and used that random number in a macro to cause a time delay then take the present time in format hh:mm:ss:000 and used the middle of the last 3 digits to add to a random prime number to seed my generator and give me my number....

 

[me2012]

the example I gave was for one "try" - If you had a thousand tries you could SUM the number of 1's in each row and use countif sum=4, 5 and 6

enough data = billions and Excel ain't big enough.......

try it - BUT to generate your "tries" you will need a random number generator - I used one once that generated a random seed and used that random number in a macro to cause a time delay then take the present time in format hh:mm:ss:000 and used the middle of the last 3 digits to add to a random prime number to seed my generator and give me my number....

@oldbrewer mate i a Hired a Freelancer to do this project for me , he used VBA and macros to do exactly what i needed + some really nice extras in 1 day, the resulting data was as expected and scraping the data is now dead easy.

Thanx for your help anyway, much appreciated. ( and yes summing with countif was what i was trying , but the freelancer showed me a more efficient way to do it ).

Quick Summary of the Results Im getting so far if your interested :

Stats eg : 15,000 draws on Sheet 10 checked against 10 combinations on sheet 1 takes 0:20sec ( 16Gb , I5 core , 64 bit )

1. It dosen't matter to a point (at least 10,000 draws) how many game draws it has , the resulting data is the same. ( Doubling up on the same result )
2. The resulting data is in pockets ( as expected ) and the timing between events on certain combinations is yielding promising results.
3. Say if i only look at every 100th drawing or 50th drawing etc , the timing between events on certain combinations is better than expected (still crunching the numbers on that ,Time will tell ).

Question : Is it possible to somehow reverse engineer the SEED from a set of Drawings that uses a pseudo random number generator (with a static seed) to produce the Draws using Excel ?

Then i could just run them in parallel and know exactly whats going to happen and when

 

[oldbrewer]

set up a pyramid scam - invite people to invest in your scheme - pay them 10% to attract new members - then after 2 weeks run for the hills.....

 

[me2012]

Ha ha ha , yes much easier (old school)

- Watch me be a mod without being one

Thread Closed