"iart" wrote
it's a /9 (3 squared)
....
so if i have 5 variables per column on sheet 1,
on sheet 2 it's "/5" and i fill down to f25,
and then on sheet three its "/25" fill down to c625,
and sheet 4 its "/25" and fill down to 15625
.... do i have that right?
Yes, believe you caught the drift <g
To illustrate ..
(for a 3 var per col in 6 cols source)
In Sheet1
-------------
Let's extend the source data
to 3 var per col in 6 cols
(from 2 per col in 6 cols)
in A1:F3
1 10 21 34 40 11
3 14 23 37 42 13
4 17 28 38 43 18
(all 18 numbers assumed unique, as before)
The above will generate into a total of:
3^6 = 729 combos in the final output Sheet4
(quite a big jump already from previous 64 [2^6] to 729 !)
In Sheet2
-------------
Just change the last number in the
parts for INT(.../2) and MOD(..,2)
in the formulas in A1:F1
to 3 (instead of 2), viz
INT((ROW(A1)-1)/3)
MOD(ROW(A1)-1,3)
then copy A1:F1 down by 9 rows (3 x 3)
(instead of previous 4 [2 x 2]) to F9
In Sheet3
-------------
In a similar vein as for Sheet2,
change the last number in the
parts for INT(.../4) and MOD(..,4)
in the formulas in A1:C1
to 9 (instead of 4), viz
INT((ROW(A1)-1)/9)
MOD(ROW(A1)-1,9)
then copy A1:C1 down by 81 rows (9 x 9)
(instead of previous 16 [4 x 4]) to C81
In Sheet4
-------------
Amend similarly as done for Sheet3,
change the last number in the
parts for INT(.../4) and MOD(..,4)
in the formula in A1
to divide by 9 (instead of 4), viz
INT((ROW(A1)-1)/9)
MOD(ROW(A1)-1,9)
then copy A1 down by 729 rows (9 x 9 x 9)
(instead of previous 64 [4 x 4 x 4]) to A729
A1:A729 will return all 729 "unique" combos
of the 18 numbers in Sheet1's A1:F3
(joined with hyphens),
viz. for the sample data in Sheet1:
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)
--
Here's a reference listing of all the formulas for the above:
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 down to A729
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----