How do you randomly assign numbers without getting duplicates?
 

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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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" wrote in message
...
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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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
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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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
http://download.cnet.com/Morefunc/30...n;overviewHead
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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!

How do you randomly assign numbers without getting duplicates?
 

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 a =TRUNC(RAND()*9004) and
=TRUNC(RAND()*9004-1)+1.
Please help!


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