Random Number Cell Filling

[Big Rick]

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

 

[Sheeloo]

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

 

[Big Rick]

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

 

[James Silverton]

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:

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

 

[Shane Devenshire]

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.

 

[RagDyeR]

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!
=====================================================


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

 

[RagDyeR]

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!
=====================================================

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!
=====================================================


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

 

[Bernd P]

Hello,

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

Regards,
Bernd

 

[Big Rick]

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]

You're making me feel old!<bg>

Glad to help and appreciate the feed-back.