Random Number Cell Filling

Big Rick · External Since: Mar 15, 2006 Posts: 12

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 · External Since: Apr 15, 2009 Posts: 35

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

Big Rick · External Since: Mar 15, 2006 Posts: 12

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

Shane Devenshire · External Since: Jan 07, 2009 Posts: 252

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

RagDyeR · External Since: Feb 17, 2004 Posts: 978

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" <BigRick DeleteThis @discussions.microsoft.com> wrote in message
news:49195377-71ED-4357-971C-1BBF9C0ACD87@microsoft.com...
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

RagDyeR · External Since: Feb 17, 2004 Posts: 978

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" <ragdyer.RemoveThis@cutoutmsn.com> wrote in message
news:Od4crK09JHA.200@TK2MSFTNGP05.phx.gbl...
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" <BigRick.RemoveThis@discussions.microsoft.com> wrote in message
news:49195377-71ED-4357-971C-1BBF9C0ACD87@microsoft.com...
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

Bernd P · External Since: Jan 26, 2009 Posts: 43

Hello,

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

Regards,
Bernd

James Silverton · External Since: Feb 05, 2009 Posts: 2

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

Big Rick · External Since: Mar 15, 2006 Posts: 12

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" <ragdyer DeleteThis @cutoutmsn.com> wrote in message
> news:Od4crK09JHA.200@TK2MSFTNGP05.phx.gbl...
> 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" <BigRick DeleteThis @discussions.microsoft.com> wrote in message
> news:49195377-71ED-4357-971C-1BBF9C0ACD87@microsoft.com...
> 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
>
>
>
>

Ragdyer · External Since: Feb 07, 2004 Posts: 985

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" <BigRick.TakeThisOut@discussions.microsoft.com> wrote in message
news:3752259A-29B2-42DC-88B5-B9BD591317B0@microsoft.com...
> 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" <ragdyer.TakeThisOut@cutoutmsn.com> wrote in message
> > news:Od4crK09JHA.200@TK2MSFTNGP05.phx.gbl...
> > 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" <BigRick.TakeThisOut@discussions.microsoft.com> wrote in message
> > news:49195377-71ED-4357-971C-1BBF9C0ACD87@microsoft.com...
> > 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
> >
> >
> >
> >