Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to assign unique number to column duplicates? | Excel Worksheet Functions | |||
Randomly add numbers in a group | Excel Worksheet Functions | |||
How to randomly assign numbers to names? | Excel Discussion (Misc queries) | |||
randomly select numbers | Excel Discussion (Misc queries) | |||
to find missing serial numbers in randomly generated numbers | Excel Worksheet Functions |