I am looking for some assistance to achieve the following which I
would prefer if possible to be able to carry out by using formula
functions rather than macros which are not my forte.

I have used the COMBIN formula to achieve the figures I require,

eg: COMBIN(6,3) to give me 20

what I need is a formula that will actually list the 20 unique
combinations,

ie:

1 2 3
1 2 4
1 2 5
1 2 6
2 3 4
2 3 5

etc. etc.

I have many of these of varying figures to carry out and to enter them
by hand would be laborious and open to human error.

I would appreciate any help or advise.

Thank you in advance,

Terry.

Terry,

I don't know of a formula that will do it but this macro. Right click the
sheet tab, view code and paste this in.

Put your numbers in column A start ing in A1 and run the code. The
combinations will be output to column B.

Sub combinations()
last = Cells(Rows.Count, "A").End(xlUp).Row
For I = 1 To last - 2
For J = I + 1 To last - 1
For K = J + 1 To last
Cells(L + 1, 2) = Cells(I, 1) & Cells(J, 1) & Cells(K, 1)
L = L + 1
Next
Next
Next
End Sub

Mike

"Terry" wrote:

I am looking for some assistance to achieve the following which I
would prefer if possible to be able to carry out by using formula
functions rather than macros which are not my forte.

I have used the COMBIN formula to achieve the figures I require,

eg: COMBIN(6,3) to give me 20

what I need is a formula that will actually list the 20 unique
combinations,

ie:

1 2 3
1 2 4
1 2 5
1 2 6
2 3 4
2 3 5

etc. etc.

I have many of these of varying figures to carry out and to enter them
by hand would be laborious and open to human error.

I would appreciate any help or advise.

Thank you in advance,

Terry.

Another play is to use Myrna Larson's power subroutine
to generate the combinations ..

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), 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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry" wrote in message
...
I am looking for some assistance to achieve the following which I
would prefer if possible to be able to carry out by using formula
functions rather than macros which are not my forte.

I have used the COMBIN formula to achieve the figures I require,

eg: COMBIN(6,3) to give me 20

what I need is a formula that will actually list the 20 unique
combinations,

ie:

1 2 3
1 2 4
1 2 5
1 2 6
2 3 4
2 3 5

etc. etc.

I have many of these of varying figures to carry out and to enter them
by hand would be laborious and open to human error.

I would appreciate any help or advise.

Thank you in advance,

Terry.

Mike & Max

Thank you both very much for your help and your fast
responses. I have decided to use the option supplied by Max as it will
be easier for me to use as my ability with Excel is quite limited.
This does not of course detract from my gratitude to you both for your
efforts.

Regards,

Terry.

Welcome, nice of you to post back here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry" wrote in message
...
Mike & Max

Thank you both very much for your help and your fast
responses. I have decided to use the option supplied by Max as it will
be easier for me to use as my ability with Excel is quite limited.
This does not of course detract from my gratitude to you both for your
efforts.

Regards,

Terry.