Unique RANDOM NUMBERS within specified range

Hi,

I have looked at the Random Number Generator Add-in and the =RAND function
but can't get either to do what I want (although I'm sure they can)

I have a range of cells in a single column (A2:A3667), and I want a list of
random numbers to fill that range using the values 1:3666, using each value
once and once only. Effectively randomising the row numbers.

How can I do this?

Cheers,

Matt

Matt,

In A2, enter

=RAND()

In B2, enter

=RANK(A2,$A$2:$A$3667)

then copy A2:B2 down to row 3667.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Matt D Francis" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have looked at the Random Number Generator Add-in and the =RAND function
> but can't get either to do what I want (although I'm sure they can)
>
> I have a range of cells in a single column (A2:A3667), and I want a list
of
> random numbers to fill that range using the values 1:3666, using each
value
> once and once only. Effectively randomising the row numbers.
>
> How can I do this?
>
> Cheers,
>
> Matt
>

Hi,

Well that worked - wasn't quite the way I was expecting to be able to do it,
but thanks! Can this guarantee that the numbers in column A will be unique?

Also, don't know if this is normal but the random numbers I generate in Col
A are all decimals, why's that?

"Bob Phillips" wrote:

> Matt,
>
> In A2, enter
>
> =RAND()
>
> In B2, enter
>
> =RANK(A2,$A$2:$A$3667)
>
> then copy A2:B2 down to row 3667.
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Matt D Francis" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have looked at the Random Number Generator Add-in and the =RAND function
> > but can't get either to do what I want (although I'm sure they can)
> >
> > I have a range of cells in a single column (A2:A3667), and I want a list
> of
> > random numbers to fill that range using the values 1:3666, using each
> value
> > once and once only. Effectively randomising the row numbers.
> >
> > How can I do this?
> >
> > Cheers,
> >
> > Matt
> >
>
>
>

Because RAND() returns values from [0...1).

Another way, without the helper column:

http://www.mcgimpsey.com/excel/udfs/randint.html


In article <[email protected]>,
"Matt D Francis" <[email protected]> wrote:

> Also, don't know if this is normal but the random numbers I generate in Col
> A are all decimals, why's that?

I cannot guarantee that the numbers in A will be unique, but in all the
times I have used it I have never seen a duplicate.

If you want to be sure, use

A2: =RAND()
B2: =RANK(A2,$A$2:$A$3667)+ROW()/10^7
C2: =RANK(B2,$B$2:$B$3667,1)

and copy down

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Matt D Francis" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> Well that worked - wasn't quite the way I was expecting to be able to do
it,
> but thanks! Can this guarantee that the numbers in column A will be
unique?
>
> Also, don't know if this is normal but the random numbers I generate in
Col
> A are all decimals, why's that?
>
> "Bob Phillips" wrote:
>
> > Matt,
> >
> > In A2, enter
> >
> > =RAND()
> >
> > In B2, enter
> >
> > =RANK(A2,$A$2:$A$3667)
> >
> > then copy A2:B2 down to row 3667.
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Matt D Francis" <[email protected]> wrote in
message
> > news:[email protected]...
> > > Hi,
> > >
> > > I have looked at the Random Number Generator Add-in and the =RAND
function
> > > but can't get either to do what I want (although I'm sure they can)
> > >
> > > I have a range of cells in a single column (A2:A3667), and I want a
list
> > of
> > > random numbers to fill that range using the values 1:3666, using each
> > value
> > > once and once only. Effectively randomising the row numbers.
> > >
> > > How can I do this?
> > >
> > > Cheers,
> > >
> > > Matt
> > >
> >
> >
> >