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!

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!

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!

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!

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!

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!

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.