 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / March 2006generate 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? 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 --- > 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 [quoted text clipped - 3 lines] > > Can I accomplish what I want to do? Thank you!!!!! I will give it a try. > One way .. > [quoted text clipped - 20 lines] >> >> Can I accomplish what I want to do? You're welcome ! Let us know how it went .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > Thank you!!!!! I will give it a try. Tried it but all of the numbers are either 0 or 1. Where did I go wrong??? > One way .. > [quoted text clipped - 20 lines] >> >> Can I accomplish what I want to do? 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. > One way .. > [quoted text clipped - 20 lines] >> >> Can I accomplish what I want to do? Glad to hear that, Stephen ! Thanks for feeding back .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > 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. 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. Insert>Name>Define that range as Indexes. Into another column, say D3:D602, enter your list of names, and Insert>Name>Define 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 > 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 [quoted text clipped - 3 lines] > > Can I accomplish what I want to do?  SignaturePlease keep response(s) solely within this thread. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.