List Possible Combinations

Welcome to the forum.

That's 500M combinations. What would you do if you had this 2GB file that you couldn't do without it?

What do you mean about any duplications? All combinations would be listed once, so there would be no duplicates. Excel can handle something that size, but it will take forever.

500M is 500 million.

Excel 2003 has 65,536 rows by 256 columns. Therefore each sheet could only hold 16,777,216 combinations. You would need 30 worksheets to hold them all.

Excel 2007 has 1,048,576 rows by 16,384 columns. Each sheet can hold over 17 billion combinations, so it would work.

While ludicrous, here's the code that will list them all in rows 1 to 1 million, spanning 500 columns. It took my laptop 1 minute 24 seconds to get through the first million, so if you multiply that out by 500 you get 42000 seconds, or 11 hours and 40 minutes. Go get a cup of tea or something.

Please Login or Register  to view this content.

What do you mean duplications? This will create a list of every possible combination of the three sets of numbers. If you mean will there be an instance of "1, 23, 450" and "450, 23,1" then yes, there will be duplicates. There won't be two instances of "1, 23, 450" though.

Of course it can be done (making three cells instead of one for every number), but will it do any good given my previous statement?

To split each combination into three cells across (e.g. A1, B1, C1; A2, B2, C2) use:

Please Login or Register  to view this content.

Once it gets to row 1 million it will go back to the top and resume in columns D/E/F, then move to G/H/I when the previous 3 columns are filled to 1 million rows.

The end result will be 1,500 columns x 1,000,000 rows of pure insanity. On my PC it would take about 12 hours to run, and who knows if it would blow up due to memory constraints or not. I'm not going to test it for beyond the 3-minute run I just did. Good luck!