Random Number Cell Filling
 

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells. I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

Big wrote on Sat, 27 Jun 2009 07:31:01 -0700:

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers
in col A to be replicated in random order in col B.

The formula also gives gives me random REF# errors in some of
the cells. I cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick

"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be
back.

In cells a1 to a20, I have 20 different numbers, obviously
1 number in each cell. In cells b1 to b20, I would like
those 20 amounts, but placed in randomly. All help
gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Can I put in a few thoughts?

The formula could use RANDBETWEEN(1,20) instead of rounding.

Another problem is that you may get identical numbers in column B. The
traditional method is to have a helper column with random numbers and
sort on that.

It's still possible but unlikely that the same value will arise but you
could use two helper columns with 1:20 in B say and RAND() in C, then
sorting on C and B and using the INDIRECT referring to B will give
really random values in D. As a bonus, this last would preserve the A
column.

Thank you Sheeloo for reminding me of the ingenious use of INDIRECT. I'd
forgotten about it.
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

Random Number Cell Filling
 

Hi,

1. Highlight A1:A20 and drag the fill handle to the right on column
2. In C1 enter =RAND() or =RANDBETWEEN(1,20) it really makes no difference
3. Fill this down to C20
4. Highlight B1:C20 and sort on C.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Big Rick" wrote:

Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells. I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

What you're looking for is a random order without replacement (no
duplicates).

In an out-of-the-way location, say Z1 to Z20, enter the Rand function:

In Z1 enter:
=Rand()
and copy down to Z20.

With your original numbers in A1 to A20, in B1 enter:

=INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20))

And copy down.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Big Rick" wrote in message
...
Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells. I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in
each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in
randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

Should mention that each time you hit <F9, you'll get a *new* random list.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
What you're looking for is a random order without replacement (no
duplicates).

In an out-of-the-way location, say Z1 to Z20, enter the Rand function:

In Z1 enter:
=Rand()
and copy down to Z20.

With your original numbers in A1 to A20, in B1 enter:

=INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20))

And copy down.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Big Rick" wrote in message
...
Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells. I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in
each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in
randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

Hello,

You can use my UDF Random_Pick:
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

Random Number Cell Filling
 

Hi RagDyer
Yours was the one that worked best and was the simplest for me, so have used
your formula. I remember you from the 'olden days'! Many thanks to you.

My many thanks also to Shane, James, and Sheeloo for your help.
It is very much appreciated from all of you.

Best regards
<-<-<-<-
Big Rick


"RagDyeR" wrote:

Should mention that each time you hit <F9, you'll get a *new* random list.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
What you're looking for is a random order without replacement (no
duplicates).

In an out-of-the-way location, say Z1 to Z20, enter the Rand function:

In Z1 enter:
=Rand()
and copy down to Z20.

With your original numbers in A1 to A20, in B1 enter:

=INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20))

And copy down.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Big Rick" wrote in message
...
Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells. I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number in
each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in
randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick

Random Number Cell Filling
 

You're making me feel old!<bg

Glad to help and appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Big Rick" wrote in message
...
Hi RagDyer
Yours was the one that worked best and was the simplest for me, so have
used
your formula. I remember you from the 'olden days'! Many thanks to you.

My many thanks also to Shane, James, and Sheeloo for your help.
It is very much appreciated from all of you.

Best regards
<-<-<-<-
Big Rick


"RagDyeR" wrote:

Should mention that each time you hit <F9, you'll get a *new* random
list.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"RagDyeR" wrote in message
...
What you're looking for is a random order without replacement (no
duplicates).

In an out-of-the-way location, say Z1 to Z20, enter the Rand function:

In Z1 enter:
=Rand()
and copy down to Z20.

With your original numbers in A1 to A20, in B1 enter:

=INDEX(A$1:A$20,RANK(Z1,Z$1:Z$20))

And copy down.
--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===


"Big Rick" wrote in message
...
Hi Sheeloo
Many thanks for you reply.

This does work, but only to a small degree for my needs.
What I should of pointed out is that I needed all 20 numbers in col A to
be
replicated in random order in col B.

The formula also gives gives me random REF# errors in some of the cells.
I
cannot figure out why.

Perhaps you could help me a little bit further.
<-<-<-<-
Big Rick


"Sheeloo" wrote:

Try this in B1 and copy down to B20
=INDIRECT("A"&ROUND(RAND()*20,0))

"Big Rick" wrote:

Hello Folks,

This is my first post in about 2½ years, it's so good to be back.

In cells a1 to a20, I have 20 different numbers, obviously 1 number
in
each
cell.
In cells b1 to b20, I would like those 20 amounts, but placed in
randomly.
All help gratefully received.

Your help is and always has been very much appreciated.
Thanking you in anticipation.
<-<-<-<-
Big Rick