Thank you very much for your help. I appreciate all the time you took to
write this out.

"Max" wrote:

Here's a formulas play for 12 numbers i.e. 2 sets of 6 numbers in 6 cols
to illustrate the possibilities ..

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 below 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

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 = 46656 combos in Sheet4
(as 7^6 = 117649, which exceeds Excel's max 65536 rows)

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Jordan" wrote:
If someone could help me with this I would really appreciate it.

If you have three sets of numbers. Say in Column A you have 1 2 & 3 in
Column B you have 4 5 & 6 and then in Column C you have 7 8 & 9.

Is there any way to see all the variations of numbers you could come up with
if you used one number from each Column. For example starting with the
number 1 in Column A you could end up with 9 different numbers, see example
below. Also, they have to be in order, so Column A number always has to be
in first place, Column B in second place and Column C in Third.

The data set I am using is much larger which is why I'm looking for a way to
handle this. Any help will be appreciated. Thanks.
147
148
149
157
158
159
167
168
169