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>
Subject: Re: displaying ranges
Date: Tue, 30 Nov 2004 18:25:07 +0800
Lines: 111
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: <#fm10bs1EHA.132@tk2msftngp13.phx.gbl>
Newsgroups: microsoft.public.excel.misc
NNTP-Posting-Host: 203.127.140.115
Path: g2news1.google.com!news3.google.com!news.glorb.com!border1.nntp.dca.giganews.com!nntp.giganews.com!newsfeed.cwix.com!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
Perhaps a set-up for 12 numbers to play around with ?
Assuming your intent is that if you have,
say 2 sets of 3 numbers in 3 cols
(all 6 numbers unique), i.e.:
1 2 3
4 5 6
you want 8 combinations
(= 2 x 2 x 2), viz.:
1-2-3
1-2-6
1-5-3
1-5-6
4-2-3
4-2-6
4-5-3
4-5-6
So, for 12 numbers,
i.e. 2 sets of 6 numbers in 6 cols:
1 2 3 4 5 6
7 8 9 10 11 12
you want: 2 x 2 x 2 x 2 x 2 x 2
= 2^6 = 64 combinations
In Sheet1
-------------
Assume the 12 numbers are in A1:F2
(all 12 numbers assumed unique)
1 10 21 34 40 11
3 14 23 37 42 13
In Sheet2
-------------
Put in:
A1: =OFFSET(Sheet1!$A$1,INT((ROW(A1)-1)/2),)
B1: =OFFSET(Sheet1!$B$1,MOD(ROW(A1)-1,2),)
C1: =OFFSET(Sheet1!$C$1,INT((ROW(A1)-1)/2),)
D1: =OFFSET(Sheet1!$D$1,MOD(ROW(A1)-1,2),)
E1: =OFFSET(Sheet1!$E$1,INT((ROW(A1)-1)/2),)
F1: =OFFSET(Sheet1!$F$1,MOD(ROW(A1)-1,2),)
Select A1:F1, copy down to F4
In Sheet3
-------------
Put in A1:
=OFFSET(Sheet2!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet2!$B$1,INT((ROW(A1)
-1)/4),)
Put in B1:
=OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$C$1,MOD(ROW(A1)-1,
4),1)
Put in C1:
=OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,4),)&"-"&OFFSET(Sheet2!$E$1,MOD(ROW(A1)-1,
4),1)
Select A1:C1, copy down to C16
In Sheet4
------------
Put in A1:
=OFFSET(Sheet3!$A$1,INT((ROW(A1)-1)/4),)&"-"&OFFSET(Sheet3!$B$1,INT((ROW(A1)
-1)/4),)&"-"&OFFSET(Sheet3!$C$1,MOD(ROW(A1)-1,4),)
Copy down to A64
A1:A64 will return all the 64 "unique" combinations
of the 12 numbers in Sheet1's A1:F4
(joined with hyphens)
1-10-21-34-40-11
1-10-21-34-40-13
1-10-21-34-42-11
1-10-21-34-42-13
1-10-21-37-40-11
1-10-21-37-40-13
.....
.....
3-14-23-37-40-11
3-14-23-37-40-13
3-14-23-37-42-11
3-14-23-37-42-13
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"iart" <i...@discussions.microsoft.com> wrote in message
news:B91E81A4-2719-4C9C-98F2-C88152787685@microsoft.com...
> ok, i figured it out.
>
> well, interesting. i tested it with 12 numbers. there are only 36
> possibilities, but the program returned 4000!
>
> 6 columns with each column having 2 distinct possibilities.
