Creating number from groups of numbers
 

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

Creating number from groups of numbers
 

This can be done on a small number of digits/columns but on a large set your
computer will dump, for example 9 digits across 9 columns doesn't seem large
but there are 387,420,489 possible combinations, that will fill column A from
top to bottom on over 5900 sheets, and thats just 1 row.

--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"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

Creating number from groups of numbers
 

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

Creating number from groups of numbers
 

How large is your data set ?

The formula should not be very difficult, but it becomes quite long as the
number of columns increases...
--
Regards,
Luc.

"Festina Lente"


"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

Creating number from groups of numbers
 

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

Creating number from groups of numbers
 

My pleasure` Jordan.
Do reply to the other responders as well
They may have alternative, possibly better insights to offer you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Jordan wrote:
Thank you very much for your help. I appreciate all the time you took to
write this out.

O.K. Maybe I'm doing something wrong, but I can't get your formulas to work right. I followed your instructions to the letter and I did get random variations, but not all of them. I figured this out by substituting the numbers you have with 1-12 and I didn't get all of the possible variations. How can I do this again and get all of the possible variations? I appreciate the help.

Generate Random numbers from a pre-defined set
 

Say we have a block of numbers from A1 thru J2000

In L1 enter:
=RANDBETWEEN(1,10)
(randon column)


In L2 enter:

=RANDBETWEEN(1,2000)
(random row)


In L3 enter:


=INDEX(A1:J2000,L2,L1)

to pick a single random item in the table

--
Gary's Student


"frankjh19701" wrote:


Does anyone know how to setup a random number generator from a
pre-defined set of numbers? For example, I have a series of numbers
across 10 columnsand down 2000 rows, and I want to select only a few of
them and run a random number analysis with them. To be able to pick 3,
5, 7, or all ten numbers and run them to find all of the possible 3, 5,
7, or 10 digit number variations. Anyone know?




--
frankjh19701

I'm sorry, maybe I'm a little slow here, but this doesn't work or make any sense to me. I don't want to pick a lone single random number, I want to pick all of one numbers variation with the other numbers in a given range. What you presented only selects one random number by itself, which doesn't help. I'm sorry to say.
Let's say you have a range of A:1 to H:1 and then all of those cells are filled with numbers (Not sequential in any way), and you want to select among them one number and find all of the possible variations of that number with all of the other numbers. Like this:
The numbers are 3, 45, 12, 103, 67, 2, 111, 23 and you want to find all of the variations (In a set of 4) that 3 contains. Does this make more sense? Let me know if it doesn't. Again, I appreciate any/all help that I can get.

(Previous Post)
Let's say you have a range of A:1 to H:1 and then all of those cells are filled with numbers (Not sequential in any way), and you want to select among them one number and find all of the possible variations of that number with all of the other numbers. Like this:
The numbers are 3, 45, 12, 103, 67, 2, 111, 23 and you want to find all of the variations (In a set of 4) that 3 contains. Does this make any sense? Let me know if it doesn't. Again, I appreciate any/all help that I can get. Could you help me with this?

My data set contains over 3000 numbers, but I only want to know a selection of them. I have tried several versions of the formulas already presented with no luck. I just have to figure out how many variations there could be if you use one number from one cell and combined it with approx. twenty others, in a set size (Meaning four to six columns). For example, if you have the numbers 3,12,69,32,112, and 77, what four to six digit combinations could you get with them? Mixing and matching these numbers correctly would reduce my research and application time greatly. Thank you in advance for your time.

Generate Random numbers from a pre-defined set
 

"Gary''s Student" wrote:

Say we have a block of numbers from A1 thru J2000

In L1 enter:
=RANDBETWEEN(1,10)
(randon column)


In L2 enter:

=RANDBETWEEN(1,2000)
(random row)


In L3 enter:


=INDEX(A1:J2000,L2,L1)

to pick a single random item in the table

--
Gary's Student


"frankjh19701" wrote:


Does anyone know how to setup a random number generator from a
pre-defined set of numbers? For example, I have a series of numbers
across 10 columnsand down 2000 rows, and I want to select only a few of
them and run a random number analysis with them. To be able to pick 3,
5, 7, or all ten numbers and run them to find all of the possible 3, 5,
7, or 10 digit number variations. Anyone know?


This works great for me ("randomly" choosing five numbers from 33).

Creating number from groups of numbers
 

I am sorry Frank. I now understand what you want, but I don't know how to do
it. I'll ask some other people at the Help Center.
--
Gary''s Student - gsnu200713

Thank you a lot. I really appreciate any/all help you can provide me. Random number sare one thing, but random from a pre-determined set of them without deviations is another.