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

### How do you randomly assign numbers without getting duplicates?

• Follow

```I'm trying to randomly assign a number between 1 and 9004 for a mailing list
in Excel. What I plan to do, is then sort cases from smallest number (1) to
largest number (9004) and take the first half to use in my sample for a
survey project. I want each 'case' in the list to recieve one number b/w 1
and 9004 with NO duplicates. The formulas I have tried using with no success
in eliminating duplicates are: =TRUNC(RAND()*9004)  and
=TRUNC(RAND()*9004-1)+1.
```
 0

```It seems to work for 10 cells so it should work for 9004, as well, if you'll
change the references.
http://img710.imageshack.us/img710/6681/nonamext.png
Micky

"Meesh" wrote:

> I'm trying to randomly assign a number between 1 and 9004 for a mailing list
> in Excel. What I plan to do, is then sort cases from smallest number (1) to
> largest number (9004) and take the first half to use in my sample for a
> survey project. I want each 'case' in the list to recieve one number b/w 1
> and 9004 with NO duplicates. The formulas I have tried using with no success
> in eliminating duplicates are: =TRUNC(RAND()*9004)  and
> =TRUNC(RAND()*9004-1)+1.
```
 0

```Meesh,

Let's say that your mailing list is in columns A to E.  Fill column F with the numbers 1 to 9004
(enter a 1, then a two, and select those two cells and pull down).  Then in column G, enter =RAND().
Then sort columns F and G by column G ascending, and then sort A to F by column F ascending.

HTH,
Bernie
MS Excel MVP

"Meesh" <Meesh@discussions.microsoft.com> wrote in message
news:E2AF7A2B-0B5B-4EEB-85E1-D2C93AB85BD4@microsoft.com...
> I'm trying to randomly assign a number between 1 and 9004 for a mailing list
> in Excel. What I plan to do, is then sort cases from smallest number (1) to
> largest number (9004) and take the first half to use in my sample for a
> survey project. I want each 'case' in the list to recieve one number b/w 1
> and 9004 with NO duplicates. The formulas I have tried using with no success
> in eliminating duplicates are: =TRUNC(RAND()*9004)  and
> =TRUNC(RAND()*9004-1)+1.

```
 0

```See John McGimpsey's site for a UDF

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

Gord Dibben  MS Excel MVP

On Mon, 4 Jan 2010 10:10:02 -0800, Meesh <Meesh@discussions.microsoft.com>
wrote:

>I'm trying to randomly assign a number between 1 and 9004 for a mailing list
>in Excel. What I plan to do, is then sort cases from smallest number (1) to
>largest number (9004) and take the first half to use in my sample for a
>survey project. I want each 'case' in the list to recieve one number b/w 1
>and 9004 with NO duplicates. The formulas I have tried using with no success
>in eliminating duplicates are: =TRUNC(RAND()*9004)  and
>=TRUNC(RAND()*9004-1)+1.

```
 0

```If you don't have something agains add-ons - you may try installing "MOREFUNC"
One of its built-in functions is MRAND.
It generates a series of random integers without(!) repetitions
Micky

"מיכאל (מיקי) אבידן" wrote:

> It seems to work for 10 cells so it should work for 9004, as well, if you'll
> change the references.
> http://img710.imageshack.us/img710/6681/nonamext.png
> Micky
>
>
> "Meesh" wrote:
>
> > I'm trying to randomly assign a number between 1 and 9004 for a mailing list
> > in Excel. What I plan to do, is then sort cases from smallest number (1) to
> > largest number (9004) and take the first half to use in my sample for a
> > survey project. I want each 'case' in the list to recieve one number b/w 1
> > and 9004 with NO duplicates. The formulas I have tried using with no success
> > in eliminating duplicates are: =TRUNC(RAND()*9004)  and
> > =TRUNC(RAND()*9004-1)+1.
```
 0

```Do RAND() first, Sort, THEN apply 1-9004 to the results.

Meesh wrote:

> I'm trying to randomly assign a number between 1 and 9004 for a mailing list
> in Excel. What I plan to do, is then sort cases from smallest number (1) to
> largest number (9004) and take the first half to use in my sample for a
> survey project. I want each 'case' in the list to recieve one number b/w 1
> and 9004 with NO duplicates. The formulas I have tried using with no success
> in eliminating duplicates are: =TRUNC(RAND()*9004)  and
> =TRUNC(RAND()*9004-1)+1.

```
 0

```Great idea, imho.

Bob I wrote:
> Do RAND() first, Sort, THEN apply 1-9004 to the results.
>
> Meesh wrote:
>
>> I'm trying to randomly assign a number between 1 and 9004 for a mailing list
>> in Excel. What I plan to do, is then sort cases from smallest number (1) to
>> largest number (9004) and take the first half to use in my sample for a
>> survey project. I want each 'case' in the list to recieve one number b/w 1
>> and 9004 with NO duplicates. The formulas I have tried using with no success
>> in eliminating duplicates are: =TRUNC(RAND()*9004)  and
>> =TRUNC(RAND()*9004-1)+1.
>

--
```
 0

6 Replies
693 Views

8/1/2012 6:38:39 PM