MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Generating unique random number

• Follow

```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?
```
 0

```In article <59bb96b5.-1@webcrossing.JaKIaxP2ac0>,
Iggles14@officeformac.com wrote:

> 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).

--
Team EM to the rescue!    http://www.team-em.com
```
 0

```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
Mike@DecisionToolworks.com

<Iggles14@officeformac.com> wrote in message
news:59bb96b5.-1@webcrossing.JaKIaxP2ac0...

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?

```
 0

2 Replies
1090 Views

Similiar Articles:

7/19/2012 8:15:46 PM