generate unique random numbers
 

I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?

generate unique random numbers
 

One way ..

Suppose serial nos (1,2,3 ... 600)
and names are in cols A and B, from row1 to 600

Put in C1: =INDEX($A$1:$A$600,RANK(D1,$D$1:$D$600))
Put in D1: =RAND()
Select C1:D1, fill down

C1 returns the random shuffle of the serial nos that's required
Press F9 key to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
. ..
I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?

generate unique random numbers
 

Thank you!!!!! I will give it a try.


"Max" wrote in message
...
One way ..

Suppose serial nos (1,2,3 ... 600)
and names are in cols A and B, from row1 to 600

Put in C1: =INDEX($A$1:$A$600,RANK(D1,$D$1:$D$600))
Put in D1: =RAND()
Select C1:D1, fill down

C1 returns the random shuffle of the serial nos that's required
Press F9 key to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
. ..
I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?

generate unique random numbers
 

You're welcome !
Let us know how it went ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
...
Thank you!!!!! I will give it a try.

generate unique random numbers
 

Tried it but all of the numbers are either 0 or 1. Where did I go wrong???


"Max" wrote in message
...
One way ..

Suppose serial nos (1,2,3 ... 600)
and names are in cols A and B, from row1 to 600

Put in C1: =INDEX($A$1:$A$600,RANK(D1,$D$1:$D$600))
Put in D1: =RAND()
Select C1:D1, fill down

C1 returns the random shuffle of the serial nos that's required
Press F9 key to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
. ..
I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?

generate unique random numbers
 

My mistake. I made an error in the formula. I corrected it and it seems to
be doing just what I wanted. Thank you very much.



"Max" wrote in message
...
One way ..

Suppose serial nos (1,2,3 ... 600)
and names are in cols A and B, from row1 to 600

Put in C1: =INDEX($A$1:$A$600,RANK(D1,$D$1:$D$600))
Put in D1: =RAND()
Select C1:D1, fill down

C1 returns the random shuffle of the serial nos that's required
Press F9 key to regenerate
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
. ..
I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?

generate unique random numbers
 

Glad to hear that, Stephen !
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Stephen Larivee" wrote in message
. ..
My mistake. I made an error in the formula. I corrected it and it seems
to
be doing just what I wanted. Thank you very much.

generate unique random numbers
 

Alternatively, get PopTools, a free add-in (Google will turn it up).
Among many very-well implemented variate generators are a host of other
functions. Among them is Shuffle. Here's how you can also keep location
and sizes of your lists more manageable:
Enter 1 through 600 (or whatever) into a column; it might be, say, in
C3:C602. InsertNameDefine that range as Indexes. Into another column,
say D3:D602, enter your list of names, and InsertNameDefine it as
NameList. Into E3:E602, array-enter (ctrl-shift-enter)
=Shuffle(Indexes), and name it ShuffledIndexes. Finally, into F3:F602,
array-enter =INDEX(NameList,ShuffledIndexes)

I mention this in part because PopTools's Shuffle command draws without
replacement, a handy thing to have. It has a lot of other excellent
stuff, and it's free!!!

HTH
Dave Braden

Stephen Larivee wrote:
I have a list of 600 names. I would like to generate a random number
between 1 and 600 for each person but not have any repeating numbers. So
far I have used
=rand()*600
This give me the correct number range but many of the numbers repeat.

Can I accomplish what I want to do?



--
Please keep response(s) solely within this thread.