Message from discussion
displaying ranges
From: "Max" <demecha...@yahoo.com>
References: <371A9C75-0DF4-4C50-A696-AFD89A078425@microsoft.com> <e938yPp1EHA.3376@TK2MSFTNGP12.phx.gbl> <B91E81A4-2719-4C9C-98F2-C88152787685@microsoft.com> <#fm10bs1EHA.132@tk2msftngp13.phx.gbl> <A58FA0A8-7004-4133-9C62-1C1FE5C9293E@microsoft.com> <B9D1C7E6-B7EE-4BC8-8654-7E2F743B7F20@microsoft.com>
Subject: Re: displaying ranges
Date: Wed, 1 Dec 2004 10:50:52 +0800
Lines: 137
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
Message-ID: <eAYwrC11EHA.804@TK2MSFTNGP12.phx.gbl>
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: 203.126.27.202
Path: g2news1.google.com!news3.google.com!news.glorb.com!wns14feed!worldnet.att.net!204.71.34.3!newsfeed.cwix.com!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
"iart" <i...@discussions.microsoft.com> 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 <at>yahoo<dot>com
----
