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