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.
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 are: =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" <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.
> 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 <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.
>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/3000-2077_4-10762487.html?tag=contentMain;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 are: =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 are: =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 are: =TRUNC(RAND()*9004)  and  
>> =TRUNC(RAND()*9004-1)+1.
>> Please help!
> 

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