Nevermore said:
The numbers can repeat which is where the list expands significantly. So I
can have 112 or 122 etc. Thats why I was looking for an automated solution.
One easy play to generate the combinations
is to use Myrna Larson's power subroutine ..
Take away this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls
(full details inside, ready to run)
In the sample file,
In Sheet1,
1. Enter the letter C or P in A1
(C = combinations, P = permutations),
eg enter: C (in your case, it's combinations)
2. Enter the number of items involved per combo in A2,
eg enter: 3 (in your case, it's 3)
3. Enter/List the N items in A3 down (your "5 possible options")
4. Select A1 (this cell selection is required),
then click the button "ListPermutations" to run the sub ListPermutations
5. The results will be written to a new sheet (just to the left),
and wrap in a zig-zag manner until all combinations are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003
--------
Go easy when you "ramp up" the generation
(increasing picks on increasing N values)
As a sanity check, for example:
a "Pick 6 out of 45" run will work out to a staggering:
=COMBIN(45,6) = 8,145,060 combinations
so almost half** an entire sheet would be populated
**A single sheet in xl97 to xl2003 houses:
=65536 rows x 256 cols = 16,777,216 cells
The sub would certainly need time to complete generation
---