I would like to have all combinations of 6 numbers in a range of number 1 - 60. The order of the combinations is not important like: 1-2-3-4-5-6 and 6-5-4-3-2-1. I discovered already that it is not possible only with Excel, so I started with VBA. Does anybody ha a good tutorial to make this kind of calculations, or can anybody help me with this?
Thank you in advance.
you do realise that there are over 50 million combinations, don't you?
what would you do with
36,045,979,200
results?
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
just 50,063,860.Originally Posted by martindwilson
order not required.
Strange enough but I do, there are 50063860 combinations. One positive point all the combinations with a sum of 314 max value and a sum of min value 66 are not necessary. I use excel 2010, but I am going to transfer this outcome to access.
oops i used permut() yep combin() =50063860
Well, as you play lottery, you probably have some spare time.
In attached file there is a macro which shall (if computer do not say "no!" before it gives you whole list) do this.
I tested it for smaller samples - 6 numbers out of 20 - 30 - runs fine, for more expect loooong times.
Of course it could be optimized. But for me it is rather a toy or a kind of "proof of concept" than a real tool so will not spend time on it.
On my computer (not so special, 3 yrs old laptop):
For 6 of 20: runtime ca. 7 seconds,
6 of 30: ca. 280 seconds (yes, almost 5 minutes!)
You can expect for 6 of 40 some 1 hour.
I'd do this step to check if it is working.
If yes - clear the workbook, and let it try for whole 6 of 60.
It shall finish in some 42 hoursBut don't blame me if it stops after 39 hrs saying "out of memory" or something like that
Do not heasitate to let us know of the result (but of course do not post the output workbook - will be huge!).
Please Login or Register to view this content.
Best Regards,
Kaper
if you're putting it into Access then youd be far better off creating the combinations in there rather than spend 2 days waiting for Excel to produce them and then another week to import into Access.
Just for Access to crash halfway through
If someone has helped you then please add to their Reputation
PJ, Probably you are right. (you have shown no working example, but my experience also says: yes, directly in access shall be better)
Probably even better - to write from the scratch in some of the quicker programming languages.
But ... here was a question on excelforum in VBA subforum.
Moreover I had the code handy from the previous thread from yesterday: http://www.excelforum.com/excel-form...ml#post3930961
which was again a piece recycled from a thread a week ago: http://www.excelforum.com/excel-prog...ml#post3928668
So a kind of Maslow's hammer situation :-P
PS. I just came to the office, where I left "6 out of 50 task" for the night on even older computer. Seems that I was a bit too pesymistic about time needed. It finished after 2 and half hours producing almost 90MB file. Columns up to P (almost) filled up. so almost 16 millions of combinations generated.
Last edited by Kaper; 12-17-2014 at 07:20 AM.
The code works perfect in excel and quite quick also!!! Thanks a lot! Now indeed because of the enormous amount of results I am going to find out where I can storage all this information, maybe in multiple excel tabs and otherwise I need to find a way to export to access.
But anyhow finally I would like to work in excel (because of my knowledge of Access). I would like to thank you again for your great code and help. If you have any suggestions further please let me know.
Hi
I've tried this code, but it misses out numbers.
e.g.: with combinations 1-59, I only get 45 million numbers where there should be 45,057,474
Noticeably 01-02-03-04-05-06 does not exist and many others
Well, as you play lottery, you probably have some spare time.
In attached file there is a macro which shall (if computer do not say "no!" before it gives you whole list) do this.
I tested it for smaller samples - 6 numbers out of 20 - 30 - runs fine, for more expect loooong times.
Of course it could be optimized. But for me it is rather a toy or a kind of "proof of concept" than a real tool so will not spend time on it.
On my computer (not so special, 3 yrs old laptop):
For 6 of 20: runtime ca. 7 seconds,
6 of 30: ca. 280 seconds (yes, almost 5 minutes!)
You can expect for 6 of 40 some 1 hour.
I'd do this step to check if it is working.
If yes - clear the workbook, and let it try for whole 6 of 60.
It shall finish in some 42 hours
Do not heasitate to let us know of the result (but of course do not post the output workbook - will be huge!).Please Login or Register to view this content.
Hi CecilP,
1) administrative: Please investt some time and read (then follow) our https://www.excelforum.com/forum-rul...rum-rules.html
You already broke rule 2 and 12
2) merit: Solution was tailored to specific needs. You may try to comment out lines with genearted sum limits:
Please Login or Register to view this content.
Welcome to the Forum! Please review our rules, then start a new thread with your question. Thanks.
Hello Dude, i'm looking for the same things, i want to generate all combinations of 6 numbers among 1-60, i just know that there is 50 063 860 combinations possible; i have more than 2000 combinations which i have to filter ; so did you find the solution how to generate all this combinations in Excel ? thank you
Please read and follow forum rules.
https://www.excelforum.com/forum-rul...rum-rules.html
Follow all of them.
This time focus especially on rule 4.
You will not receive any further answers in this thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks