Can excel list combinations

[BOJO]

Hi there All
Can anyone help me ? I am trying to find a way of...... Example

1,2,3,4,5,6 I want to write a formular that will list in the spreadsheet
the combinations of say 3s. The answer to this would be:
123 - 124 - 125 - 126 - 134 - 135 - 136 - 145 - 146 - 156
234 - 235 - 236 -245 - 246 - 256 - 345 -346 - 356 - 456

I have tried using COMBIN but can only seem to get a total ie COMBIN (6,3)
ans 20
Can excel list these combinations ? I'm not sure

Thanks in advance
BOB

 

[Max]

One way is to use Myrna Larson's power subroutine ..

Try this implemented sample from my archives:
http://www.savefile.com/files/518493
MyrnaLarson_Combination_Permutation.xls
(full details inside, ready to go <g>)

In the sample file,

In Sheet1,

1. Enter the letter C or P in A1 (C = combinations, P = permutations),
ie enter: C
2. Enter the number of items involved per combo in A2, ie enter: 3
3. Enter/List the 6 items in A3 down, ie list in A3:A8 :1, 2, ... 6
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 appear like below, in a zig-zag manner*
until all combos are exhausted:
*if it exceeds the rows limit of 65536 in xl97 to xl2003

1, 2, 3
1, 2, 4
1, 2, 5
1, 2, 6
....
4, 5, 6

Go easy when you ramp up the generation ...
eg a "Pick 6 out of 45" run works 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