How to create a complete list of combinations from 5 options

Nevermore

unread,

Jun 14, 2008, 7:59:00 PM6/14/08

to

I have a list of 5 possible options that I need to assemble into combinations
of 3 (order does not matter). I want to make sure the list is complete and
I'd rather not do it by hand. Can excel (or access) help me with this and how?

The order of the variables doesn't matter so 121 is the same to me as 112 or
211.

Gary''s Student

unread,

Jun 14, 2008, 9:10:00 PM6/14/08

to

If you are pick 3 of 5 (no repeats):

1, 2, 3
1, 2, 4
1, 2, 5
1, 3, 4
1, 3, 5
1, 4, 5
2, 3, 4
2, 3, 5
2, 4, 5

--
Gary''s Student - gsnu200791

Nevermore

unread,

Jun 14, 2008, 10:26:00 PM6/14/08

to

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.

Mike Middleton

unread,

Jun 14, 2008, 10:25:05 PM6/14/08

to

Nevermore -

Based on your description, I think you want the "number of combinations with
repetition" (according to the wikipedia entry for "combination").

So, for n=5 and k=3, the number of combinations with repetition is
(n+k-1)!/(k!*(n-1)!) = 7!/(3!*4!) = 35.

Now you know how many combinations you need to find so that "the list is
complete."

Maybe another poster will help so that you won't need to "do it by hand."

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

"Nevermore" <Neve...@discussions.microsoft.com> wrote in message
news:14702E91-D84B-4B42...@microsoft.com...

Max

unread,

Jun 14, 2008, 10:44:00 PM6/14/08

to

"Nevermore" wrote:
> 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Herbert Seidenberg

unread,

Jun 14, 2008, 11:53:13 PM6/14/08

to

Assuming with Mike Middleton that you want
combinations with repetition, 35 in this case,
and you don't want to use a macro,
then try this tandem Pivot Table approach:
http://www.savefile.com/files/1610285

Malcolm

unread,

Jul 5, 2009, 11:04:01 AM7/5/09

to

"Herbert Seidenberg" wrote:

I tried going to that URL but it says "file not found."