Randomize list of integers beteen 1-x with no repeats

[Guest]

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!

 

[James Silverton]

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

 

[Sven Pran]

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

 

[Guest]

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.

 

[Guest]

thank you!! that works perfectly for what I need.

 

[Bernd]

Hello,

I suggest to take my UDF
http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd