Generating unique random number

[Iggles14]

Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I need to select 5 items from a list of 50. When I use RANDBETWEEN(1,50), I get duplicates more often than not. Is there a different function or some simple way to generate 5 unique numbers?

 

[Carl Witthoft]

Version: 2008
Operating System: Mac OS X 10.6 (Snow Leopard)
Processor: Intel

I need to select 5 items from a list of 50. When I use RANDBETWEEN(1,50), I
get duplicates more often than not. Is there a different function or some
simple way to generate 5 unique numbers?

I of course would strongly recommend R language "sample()" or the
equivalent in Matlab.

Your difficulty is that you don't want a single random sample, nor do
you want 5 random samples. What you want is the first five elements of
a random permutation of the values 1 thru 50. And by the way,
apparently you are looking only for random integers?

I doubt you can achieve what you want in Excel (not counting painful
macro function writing).

 

[Mike Middleton]

Iggles14 -

The simplest way, especially if you only need to do it once, is to use a
helper column.

In an adjacent column, enter =RAND() and copy down. (Optionally, edit-copy
and edit-paste-special-values.) Choose Data > Sort to sort the two columns.
(If you didn't paste-special-values, new random numbers will appear; ignore
them.) Then use the top five as your "sample without replacement."

- Mike
http://www.MikeMiddleton.com
(e-mail address removed)




Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor:
Intel

I need to select 5 items from a list of 50. When I use RANDBETWEEN(1,50), I
get duplicates more often than not. Is there a different function or some
simple way to generate 5 unique numbers?