Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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!
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 561
Default 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!

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,441
Default 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!



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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!


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 561
Default 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!



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,819
Default 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!


  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to assign unique number to column duplicates? Heather Excel Worksheet Functions 3 January 4th 10 11:37 AM
Randomly add numbers in a group Michelle Excel Worksheet Functions 6 October 16th 09 09:36 PM
How to randomly assign numbers to names? Empress Excel Discussion (Misc queries) 6 September 10th 06 10:27 AM
randomly select numbers jollycarrier Excel Discussion (Misc queries) 2 August 18th 06 02:54 PM
to find missing serial numbers in randomly generated numbers B.H. Hadi Excel Worksheet Functions 2 December 1st 05 11:56 PM


All times are GMT +1. The time now is 09:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"