Combinations from values in a column

[Andreas]

Hi there,

I'm trying to figure out how to generate all combinations from a list
of N values in a column, let's say column A.

In particular, I want to have all combinations of 2 values, 3 values,
4, ... up to 7 values.

To give an example: Let's assume I have a list of only 3 values
(1,2,3) for which I want to have all combinations of two values. In
this case, the result would be 1,2; 1,3; and 2;3. The ordering of the
values does not matter, i.e. duplicates should be eliminated.

I hope I could explain it sufficiently. I would be grateful for any
help.

Thanks,
Andreas

 

[Eugene Gerber]

Use the Combin() function,

For your example, to select 2 object from a list of three use =Combin(3,2).

And just for interest's sake, if order did matter you should use the
Permut() function.

Hope this is what you need.

 

[Max]

One 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

2. Enter the number of items involved per combo in A2, eg enter: 2
(this fig would be your: 2 values, 3 values, 4, ... up to 7 values.)

3. Enter/List the N items in A3 down (your "N values in a column")

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 combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003
--------
Repeat the runs for each of your desired values (Step 2 above): 3,4,5 ... 7

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
(You sure you want to do this ??)

**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

 

[graefe.andreas]

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
(You sure you want to do this ??)

**A single sheet in xl97 to xl2003 houses:
=65536 rows x 256 cols = 16,777,216 cells

Thanks, this is exactly what I need. However, my largest problem is 7
out of 66 = 778,789,440 combinations. Is there a possibility to do
this in Excel? Or save the data in a txt file and then read it by
another application? What I need later is the median and mean of each
of the combinations.

Thanks,
Andreas

 

[graefe.andreas]

Mh, as I said, I need the mean / median of the combinations. Is it
possible to integrate this already in the code? Right now, it saves
the combinations separated by comma, which makes it impossible to
calculate the median / mean.

Andreas

 

[Max]

Thanks, this is exactly what I need...

Welcome. Try posting in .programming for your new queries.

---

 

[graefe.andreas]

Welcome. Try posting in .programming for your new queries.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Thanks, Max

 

[Dana DeLouis]

... However, my largest problem is 7 out of 66 = 778,789,440 combinations.

... save the data in a txt file and then read it by another application?
What I need later is the median and mean of each.

For SPSS, I assume you will generate all 778,789,440 subsets, and then
"Count" how many of each "mean" you have. (I'll skip Median for now).
I'll just throw this out for consideration. Your "Mean" problem could be
simplified with a Generating Function:
I won't list the vba code, but here is a math program to explain one way to
do this very quickly.
Here is the generating function for your means: (y is your subset size of 7
later)

gf = Product[1 + x^k y, {k, 66}];

Hence, we can calculate "All" Means right away from the series...

Means = CoefficientList[Coefficient[gf, y^7], x] // Rest;

(I dropped the zero indexed term)

What I mean here by Mean is the sum of your 7 numbers in each subset prior
to dividing by 7.

The smallest subset is {1,2,3,4,5,6,7} whose total is 28.
There is only one subset that totals 28

Means[[28]]
1

or in other words, has a mean of 4.
28/7.
4.

We can immediately tell that the largest count is a total of 234 (& 235)

Means[[234]]
6,327,599

234/7.
33.4286

In other words, we can immediately see that there are 6,327,599 subsets of
size 7 that have a mean of 33.4286 from a set of 66.

As a quick check, if I total all the solutions...
Means // Total
778,789,440

It matches the expected sum.
Binomial[66, 7]
778,789,440

Which checks with Excel:
=COMBIN(66,7)
778,789,440

- -
HTH :>)
Dana DeLouis