VBA Repeating triples, quads etc

[simmcity]

Frank, thank you for your detailed response! Information provided is very interesting. But again I can assure you that there are more than 5 repeated combinations of 10. A few of them I even know. They were posted in the local lottery players magazine. So how did they know about them? They know all repeated combinations of 5,6,7,8,9,10. They provided statistical information. How? But they post very rarely. That's why I'm here.
Thanks again, Frank!

- simmcity

 

[Frank]

Well theres always the possibility of a flaw in my reasoning here, which is why I mentioned that possibility. I would ask if some combinations of 10 are repeating, then how many are not there at all ? The vast majority I would have thought. I woud be more interested in those.
Maybe someone has access to a supercomputer, and they let it run for a while ? Nobody said it was impossible, but I still stck by my suggestion that 5 of anything from 8,911,710 possibles is not a meaningful or useful sample to do anything useful with.

 

[PAB]

Hi Frank,

As always, an informative, thought prevoking and excellently contrived post and along with the statistics stated has put it into perspective. I always enjoy your posts as you always have something worth while to add as well as looking at the specific task from another angle.
I did think from the outset that this was going to be a mammouth task but thought I would put some time and effort into it to see if there was a reasonably easy way to achieve it.

To work out the odds on a spreadsheet for all the various choices of numbers of balls to match you can use the HYPOGEOMDIST function. =HYPOGEOMDIST(number succeeeded in matching, number you promised to match, number of balls drawn, highest ball number ).

So for The Keno if you want to match 10 balls the probability is =HYPOGEOMDIST(10,10,20,80) = 1.12212E-07 giving odds of 8,911,710.18 to 1.

If you are choosing to match 4 balls the same formula would be =HYPOGEOMDIST(4,4,20,80)= 0.003063392 giving odds of 325.4355 to 1.
If you are feeling keen you can (and probably already have) created a table in this way for 1,2,3,4,5,6,7,8,9,10 balls you hope to match.

I've uploaded mine for reference anyway. ..http://www.mediafire.com/view/?rdb9z6ctz736oou

I like the uploaded statistical table Frank, thanks for taking the time to produce that.

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.

 

[PAB]

Hi simmcity,

But again I can assure you that there are more than 5 repeated combinations of 10. A few of them I even know. They were posted in the local lottery players magazine. So how did they know about them? They know all repeated combinations of 5,6,7,8,9,10. They provided statistical information. How?

I have no doubt that this can be achieved.
I would think that the results in the "lottery players magazine" were contributed by a professional programmer and an above average specification computer.
One observation though, if something could be put together to produce this data which would involve a heavy load on the computer resources and a very long runtime, you have to think about how often you are going to run the program.
I agree with Frank when he said what useful information would you actually get considering that this is a BIG Lotto in terms of COMBINATIONS that are relevant.
I think that if this Lotto had been running for a considerable number of years then the data to analysis might give some interesting and worth while results.

I will however, keep mucking about with it, time permitting, and see what I can come up with for you!

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.

 

[PAB]

Hi simmcity,

I will however, keep mucking about with it, time permitting, and see what I can come up with for you!

OK.
I have uploaded a file that will produce ALL Decuples that have 1 or MORE MATCHED OCCURANCES.
The code is currently set for 1 combination so you will need to change the range (I have commented the line to change) for how many draws you want to run it for.
It will, as I said previously, take a VERY LONG TIME to compile and output.
Please let me know how you get on!

http://www.mediafire.com/view/?tug6emw664u0usl

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.

 

[samsung]

Hi PAB,

it's me, simmcity. Could you please convert it to XLS format?

There is something very weird with the forum at the moment. Lost my password, can't create account and can't contact anyone. What's happening?

Anyway, thank you for everything! BIG THANKS! I will post a more detailed response on this later.

Thanks again!

 

[PAB]

Hi simmcity,

Could you please convert it to XLS format?

I don't understand your question.
It IS an Excel WorkBook.
What is the problem or error you are getting?

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.

 

[samsung]

Hi PAB

it's xlsm - Excel 2007 (or above)
I can't open it in Excel 2003
Please save as Excel 2003 file

Thank you!

 

[PAB]

Hi simmcity,

Hi PAB

It's xlsm - Excel 2007 (or above).
I can't open it in Excel 2003.

It is an Excel Macro-Enabled Workbook.
Have you got your security set to ALLOW MACRO'S?
I will upload an amended file in a moment.

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.

 

[PAB]

Hi simmcity,

It's xlsm - Excel 2007 (or above).
I can't open it in Excel 2003.

Here is the amended file for Excel 97-2003.

http://www.mediafire.com/view/?s4hgku7rqct10xe

Please let me know if it works!

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.

 

[samsung]

Hi PAB,

Thank you for your effort, but I keep getting the same error all the time: Run Time Error "13". Type mismatch.
VBA breaks on that line - Columns("A:XFD").AutoFit
Probably I should update to a newer version of Excel to get rid of the problem.
But that's not a serious error I assume, because macro works anyway and I got the following:
50,53,17,63,72,7,54,49,23,36 1 50,17,54,49,23,35,64,61,22,42 1 53,72,7,54,35,64,19,22,11,42 1
(65k rows)
Can you explain please what that mean?

Thank you! Well done

 

[PAB]

Hi simmcity,

Thank you for your effort, but I keep getting the same error all the time: Run Time Error "13". Type mismatch.
VBA breaks on that line - Columns("A:XFD").AutoFit.
But that's not a serious error I assume?

No sorry simmcity, I should have amended the code to incorporate the fact that you are using Excel 2003.
Replace that line of code with this:

Columns("Y:IV").AutoFit

Up to and including Excel 2003 there are only 256 columns going from A:IV as oppposed to Excel 2007 where there are 16,384 columns going from A:XFD.

50,53,17,63,72,7,54,49,23,36 1 50,17,54,49,23,35,64,61,22,42 1 53,72,7,54,35,64,19,22,11,42 1
(65k rows)
Can you explain please what that mean?

Well they are the 10 Number Combinations from 20 Numbers that you wanted with the total occurances they have been drawn.

Why do I get the feeling that we are NOT on the same page here?

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.

 

[PAB]

Just for your information simmcity,

There is something very weird with the forum at the moment. Lost my password, can't create an account and can't contact anyone.

If you email LT who is this Boards Administrator, he will be able to let you know what your password is.
Just click the Contact Us at the bottom of the screen.

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.

 

[PAB]

Hi simmcity,

I have just done some calculations on what the expected output will be.
We start with 256 columns for Excel 2003.
There are already 24 columns used for the drawn data.
I ran the first 5 combinations and it produced 983,025 Results using 30 columns.
That leaves us with 256 - 24 - 30 = 202 available columns left after running 5 combinations.

Starting from scratch with just the used columns from A:X for the drawn data, here is the breakdown of the available cells for output.
There are 232 columns with 65,535 cells per column because the first cell of each column is not being used, this equates to 15,204,120 cells in total to be used for both the COMBINATIONS and the NUMBER OF OCCURANCES.
As you can see, there is insufficient columns and cells to incorporate the output.

I know that you said that you were not a NOOB when it came to VBA so I would suggest that you adapt the code to output to numerous sheets.
I would also suggest that you add some additional code to SORT the data, firstly on the NUMBER OF OCCURANCES and then on the actual COMBINATION itself.
Please let me know how you get on.

Good luck!

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.

 

[PAB]

Hi simmcity,

How have you got on?
Did you try running the program to completion, and if so, were my evaluations correct with regard to the fact that there were NOT enough available cells to accomodate the data required using the version of Excel that you are using?
I would appreciate any feedback that you have to offer.
I would also like to point out that if you ask for help and recieve responses, regardless of whether or not they were helpful, and you don't leave feedback, that forum members will probably ignore any future requests.

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.

 

[PAB]

Hi simmcity,

I just had a thought, don't laugh!
I am wondering if OpenOffice would be a get around for the fact that you are using Excel 2003 and the limitations that accompany it. I have just quickly Googled if OpenOffice supports VBA and from the look of it there is a possibility. I will follow this avenue of enquiry up tomorrow and report back, just a thought.


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.

 

[samsung]

Hi PAB,

I have just done some calculations on what the expected output will be.
We start with 256 columns for Excel 2003.
There are already 24 columns used for the drawn data.
I ran the first 5 combinations and it produced 983,025 Results using 30 columns.
That leaves us with 256 - 24 - 30 = 202 available columns left after running 5 combinations.

Starting from scratch with just the used columns from A:X for the drawn data, here is the breakdown of the available cells for output.
There are 232 columns with 65,535 cells per column because the first cell of each column is not being used, this equates to 15,204,120 cells in total to be used for both the COMBINATIONS and the NUMBER OF OCCURANCES.
As you can see, there is insufficient columns and cells to incorporate the output.

Totally agree with your suggestion... I've been thinking a lot about this lately.

I just had a thought, don't laugh!
I am wondering if OpenOffice would be a get around for the fact that you are using Excel 2003 and the limitations that accompany it. I have just quickly Googled if OpenOffice supports VBA and from the look of it there is a possibility. I will follow this avenue of enquiry up tomorrow and report back, just a thought.

I'm not gonna lugh!

I don't know what to say about OpenOffice since I never used it

But anyway I think it's a good idea.

Here's a few of my thoughts. I've measured execution time of the following code:

for a:=1 to 1000000000 do inc(b);

Execution time of code:

Delphi 0,35 sec
Excel VBA 15 sec

What are you think about that? I wonder how fast our task would take in Delphi vs EXCEL VBA?

Sorry I didn't reply sooner... just had my mind on other things lately..
I'm a little busy at the moment with work stuff. I'm also trying my hand at making some additions to your macro.

Good luck,
simmcity

 

[PAB]

Hi simmcity,

You have obviously had no joy with retrieving your original password from LT and assume that you will be using samsung from now on?
So let's start again!

Hi samsung,

Thanks for the reply.

Here's a few of my thoughts. I've measured execution time of the following code:

for a:=1 to 1000000000 do inc(b);

Execution time of code:

Delphi 0,35 sec
Excel VBA 15 sec

What do you think about that? I wonder how fast our task would take in Delphi vs EXCEL VBA?

Well I can't really comment on that as I have not had any dealings or experience with Delphi.

I'm also trying my hand at making some additions to your macro.

I would be interested in seeing what you come up with.

Enjoy the rest of your weekend .

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.

 

[PAB]

Hi samsung,

Originally Posted by samsung
I'm also trying my hand at making some additions to your macro.

How is the updating of the Macro coming along, have you had any luck yet?

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.

 

[PAB]

Hi samsung,

I assume that you have given up with this or not had the time to address it???

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.