Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
I need to randomly select names (can be represented by integers) from a
list, to create a new list of those names, using all the names with no repeats. This is a duty roster that changes monthly. I want to change the order each month using all the names. I have tried to do this with RAND and Data Analysis Tools, but can't get rid of the repeats or use all the numbers. The pattern option in the Data analysis Tools insists upon repeating values. Ideally, I would like to not have anyone on last month's list in the same position on this month's list, but one hurdle at a time. any ideas? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
JB wrote on Sat, 21 Apr 2007 09:10:01 -0700:
J I need to randomly select names (can be represented by J integers) from a list, to create a new list of those J names, using all the names with no repeats. This is a duty J roster that changes monthly. I want to change the order each J month using all the names. I have tried to do this with RAND J and Data Analysis Tools, but can't get rid of the repeats or J use all the numbers. The pattern option in the Data analysis J Tools insists upon repeating values. Ideally, I would like J to not have anyone on last month's list in the same position J on this month's list, but one hurdle at a time. any ideas? J Thanks! I'm not going to address the "ideal" situation but you can make a column with integers from 1 to N followed by a column with RAND() and sort the RAND column with the other attached. I admit that the idea has been mentioned many times before. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
"JB" wrote in message ... I need to randomly select names (can be represented by integers) from a list, to create a new list of those names, using all the names with no repeats. This is a duty roster that changes monthly. I want to change the order each month using all the names. I have tried to do this with RAND and Data Analysis Tools, but can't get rid of the repeats or use all the numbers. The pattern option in the Data analysis Tools insists upon repeating values. Ideally, I would like to not have anyone on last month's list in the same position on this month's list, but one hurdle at a time. any ideas? Thanks! You have already had answers to your first problem. However: "I would like to not have anyone on last month's list in the same position on this month's list". This requirement conflicts with the request for a random shuffle! The probability that nobody will be in the same position twice is far less than 50%, I believe it is in the order of 35% (I can't be bothered to figure it out again). (You might be interested to learn that a similar condition with the main German cipher system during WW2 resulted in a deficiency that led the cipher to be broken by first the Poles and then the British intelligence services) regards Sven |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
Sven,
I was thinking that there might be a loop of some sort with an If statement that compares the newly generated random value to the previous value in a reference column, and if it is equal to the previous value, it generates another random value until it finds one that is not equal...then moves on. Is there a kernel of possibility there? Each successive value would...oh, I'd be back in the same boat with repeated values wouldn't I. "Sven Pran" wrote: "JB" wrote in message ... I need to randomly select names (can be represented by integers) from a list, to create a new list of those names, using all the names with no repeats. This is a duty roster that changes monthly. I want to change the order each month using all the names. I have tried to do this with RAND and Data Analysis Tools, but can't get rid of the repeats or use all the numbers. The pattern option in the Data analysis Tools insists upon repeating values. Ideally, I would like to not have anyone on last month's list in the same position on this month's list, but one hurdle at a time. any ideas? Thanks! You have already had answers to your first problem. However: "I would like to not have anyone on last month's list in the same position on this month's list". This requirement conflicts with the request for a random shuffle! The probability that nobody will be in the same position twice is far less than 50%, I believe it is in the order of 35% (I can't be bothered to figure it out again). (You might be interested to learn that a similar condition with the main German cipher system during WW2 resulted in a deficiency that led the cipher to be broken by first the Poles and then the British intelligence services) regards Sven |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
thank you!! that works perfectly for what I need.
"James Silverton" wrote: JB wrote on Sat, 21 Apr 2007 09:10:01 -0700: J I need to randomly select names (can be represented by J integers) from a list, to create a new list of those J names, using all the names with no repeats. This is a duty J roster that changes monthly. I want to change the order each J month using all the names. I have tried to do this with RAND J and Data Analysis Tools, but can't get rid of the repeats or J use all the numbers. The pattern option in the Data analysis J Tools insists upon repeating values. Ideally, I would like J to not have anyone on last month's list in the same position J on this month's list, but one hurdle at a time. any ideas? J Thanks! I'm not going to address the "ideal" situation but you can make a column with integers from 1 to N followed by a column with RAND() and sort the RAND column with the other attached. I admit that the idea has been mentioned many times before. James Silverton Potomac, Maryland E-mail, with obvious alterations: not.jim.silverton.at.comcast.not |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Randomize list of integers beteen 1-x with no repeats
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I randomize a list of 20 people? | Excel Discussion (Misc queries) | |||
Need ideas to randomize list monthly? | Charts and Charting in Excel | |||
How do I randomize a list without repeats | Excel Discussion (Misc queries) | |||
A list of Consecutive Integers, can I search for missing integers | Excel Worksheet Functions | |||
How do I sort a list of cities in US by randomize in MS Excel or . | Excel Discussion (Misc queries) |