lottery

Hi, i know by the formula =combin(49,6) how many combination of 6 numbers there are from a available 49 numbers (13983816).

how do i manage to get all combinations listed in a excel spreadsheet? I would want the numbers in individual cells. For example, Row 1 would show 6 numbers individually from A1 - F1.

I do realise that excel does not have enough rows to do this so may have to continue on other sheets.

As you may have guessed i am doing something on the lottery numbers and also would want it to generate a bonus number in column G.

Formulas in excel as oppose to excel VB code is preferred.

Many thanks.

From the lack of responces and from noticing the lack of respones in other similar request threads - im kinda thinking it is really hard to do or nobody knows how.

I appreciate a lot of storgae would be required for something like this. To use as less sheets as possible, additional columns on the same sheet could be used eg; a-g and then I-O.

I wait and hope for a solution.

Thanks for your responses!!

I'm ok with Excel and i do realise this is a bit of a brain stormer - for me anyway!

File size is not an issue and i do realise that there would be plenty of sheets required. Im using excel 2003 by the way.

The reason i need to do this in excel is because i wish to then be able to manipulate and analyse the results and so cannot use a ready made app like those found on the internet.

Ultimately, it does not matter if this is acheived via VBA code or formulas.

Sorry guys, i still want these combinations as ive actually thought about doing this for a veeeery long time. Unfortunately, im not that confident in VBA.

Columns A-G, I-N, Q-V, Y-AD, AG-AL, AO-AT, AW-BB. If filled down to 65536th row = a 21.7meg file.

65536 rows * 7 column sets = 458752 different combinations in a sheet.
combin(49,6) = 13983816
13983816 / 458752 = 30 sheets required!!

I think thats possible. Each sheet will obviously be a fraction larger when adding a bonus number and after incorporating the analysis i would do later.

Come on guys, let me worry about the size and number of sheets required issue. I just need to solution or even a hint so i can make a start.

RICHARD:
I'm not wanting to analyse each weeks results, but wanting to analyse and look into ALL the combinations. I have some querys and with which i wish to interrogate each combination. This is nothing to do with real weekly results.

OR, Lets make it easy for now:

how would i do the same for a lesser amount of numbers. Lets say i want all combinations out from 1-10. I still want 6 numbers selected and a seperate bonus number from 1-10.

Any ideas?

BY THE WAY,
from the comments above, i think you guys think im wanting this all on 1 .xls file. It could all be spread across many files and not all on many sheets in 1 file; which would be silly!

when running the formula/VBA code in a new .xls file, it would need to be aware that it is continuing from another file. not sure how this could be done as otherwise the code would start from scratch in each new file. maybe if in the new file the first combination could be manually inputted which would be the same as the last combintaion in the previous file and so it would continue off that?

martindwilson: its not the extact answer i was looking for, but thanks anyway! the link you provided does give a interesting tool.

thanks

The following will ask for the upper number for the combinations. It will then populate the sheet with the possible combinations, entering one possibility per cell. It enters the combinations as such 01,02, 03, 04, 05, 06. It will fill down to row 65,000 before moving to the next column.

I added a basic timer that will display the number of seconds it took to run the macro.

Please Login or Register  to view this content.

It took a little over 4 minutes to all combinations for max number of 40.

thanks anyway! the link you provided does give a interesting tool.


Free maplestory nx