I have 5 columns with a list of items in each (all text entries). I want to
create a sixth column with every permutation of items from each column.

I started by inserting a formula where I held all but one cell as an
absolute reference. When I filled down, the one cell that remained relative
changed and I had the first set of permutations; 4 items (the first one from
each of the first 4 columns) stayed the same, the last item changed. This
works fine but it is time-consuming and difficult given the number of items
in each column (one column has 20 items).

I tried pivot tables but it doesn't seem as if they are meant for this (I
might be wrong).

Please let me know what is the best way to do this.

Thanks,

Bram

One way to generate it using formulas is illustrated
in this sample construct from my archives:

http://www.savefile.com/files/101884
Generating combinations from column data.xls
(nicely rendered, full details)

In the sample,

We assume a source of 3 variables per col
within 6 cols in Sheet1's A1:F3, viz:

1 10 21 34 40 11
3 14 23 37 42 13
4 17 28 38 43 18

(All 18 source numbers above are assumed unique.
In your instance, these 18 source items would be text)

and we want to "generate" the above into a total of:
3^6 = 729 combos in a final output Sheet4, ie:

1-10-21-34-40-11 (< in A1)
1-10-21-34-40-13
1-10-21-34-40-18
....
....
4-17-28-38-43-11
4-17-28-38-43-13
4-17-28-38-43-18 (< in A729)

Steps:

In Sheet2
-----------
Put in:
A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/3),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,3),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/3),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,3),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/3),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,3),)

Select A1:F1, copy down to F9

In Sheet3
-------------
Put in A1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)-1)/9),)

Put in B1:
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,9),1)

Put in C1:
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,9),1)

Select A1:C1, copy down to C81

In Sheet4
------------
Put in A1:
=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)-1)/9),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,9),)

Copy A1 down to A729. This will list all 729 (3^6) combinations
from the source data in Sheet1's A1:F3.

Adapt to suit .. The method limits a max extension
for the source data to 6 var per col in 6 cols (in Sheet1's A1:F6)
which'll generate 6^6 = 46,656 combos in Sheet4
(as 7^6 = 117,649, which exceeds Excel 2003's max 65536 rows)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"BramL" wrote:
I have 5 columns with a list of items in each (all text entries). I want to
create a sixth column with every permutation of items from each column.

I started by inserting a formula where I held all but one cell as an
absolute reference. When I filled down, the one cell that remained relative
changed and I had the first set of permutations; 4 items (the first one from
each of the first 4 columns) stayed the same, the last item changed. This
works fine but it is time-consuming and difficult given the number of items
in each column (one column has 20 items).

I tried pivot tables but it doesn't seem as if they are meant for this (I
might be wrong).

Please let me know what is the best way to do this.

Thanks,

Bram