Random generation of employee assignments

[Blue Max]

We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning of
each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain tasks
(task numbers) included in the comprehensive list of tasks?

Thank you for any help with this question.

 

[JoeU2004]

We have 65 work assignments and must randomly re-assign
them to our employees each month. We have given each
work assignment a number 1-65. How do we randomly re-shuffle
a list of 45 whole numbers at the beginning of each month?

I'm confused. Do you have the same number of work assignments as employees?
Or do you have 45 employees that you need to assign randomly to 65 work
assignments?

The first (same number) is easy.

Ostensibly, create a column of 65 cells, say A1:A65, with the formula
=RAND(). Then create a column of 65 cells, say B1:B65, with the following
formula in B1 and copy it down:

=RANK(A1,$A$1:$A$65)

Pay close attention to the use of relative and absolute references.

Gotcha!.... The RAND() values will change every time you modify any(!) cell
in the workbook and every time you open the file, normally. Presumably that
is not what you want.

One simple way to avoid that is use the following macro and replace =RAND()
with =myrand():

Function myrand(Optional arg)
Dim first
If first = 0 Then Randomize: first = 1
myrand = Rnd
End Function

The optional arg makes it easy to generate a new set of random values.
Simply use =myrand($C$1). Then, a new set of random values are generated
whenever C1 is changed (e.g. pressing Delete).

Once we have learned how to perform the random
re-shuffle above, we would also like to learn
how to tailor the random assignments as follows:

Applying these constraints are feasible. But they require, or at least are
best implemented by, a macro. A good design of that macro is non-trivial
and goes beyond the scope of what I can deal with here. My suggestion is
that you higher an experienced Excel/VB programmer to implement it.

FYI, a "bad design" is certainly easier. A "bad" design will iterate the
random selection until the necessary constraints are met. Such a design is
not guaranteed to work in a finite amount of time, and if it does work, it
may or may not take a very long time, depending on stochastic properties.
Caveat emptor!


----- original message -----

 

[Blue Max]

Thanks Joe. In our case, we have 65 employees and 65 unique schedule
configurations (not tasks) that combine specific tasks in differing order.

I appreciate your suggestions, especially the controllable macro. How about
using the 'Data Analysis' pack included with Excel? Does it include any
additional functionality?

Thanks

*********************

 

[JoeU2004]

How about using the 'Data Analysis' pack included
with Excel? Does it include any additional functionality?

Sorry, I'm not familiar with that.

we have 65 employees and 65 unique schedule configurations (not tasks)
that combine specific
tasks in differing order.

I provided a solution for exactly what you asked for initially, namely:
"How do we randomly re-shuffle a list of 45 whole numbers [...]?"

But I suspect the following might be more useful.

Suppose you have a column of configurations in A1:A65 and a column of
employees in C1:C65. Fill D165 with the formula =myrand() or =RAND().
Then put the following formula into B1 and copy down through B65:

=INDEX($C$1:$C$65,RANK(D1,$D$1:$D$65))

Again, pay close attention the use of relative and absolute references.

This randomly assigns employees in B1:B65 to respective configurations in
A1:A65.


----- original message -----

 

[Blue Max]

Thanks Rob, this looks promising, but will take me awhile to fully digest.
When I get a handle on your example I may have more questions. Any way to
send me a sample worksheet with your example? Meanwhile, I do have one
question. What if the employee has several tasks they are incapable or
unauthorized to perform? Your example, on the surface, appears to only
accommodate one invalid task.

For example, can the list of unauthorized tasks for an employee be saved as
an array and used in the process to narrow the available population of tasks
for that employee? For example, can we start with a total array of 65
tasks, reduce it by any limitations, randomly select a task for the first
employee, and then pass the residual array on to the subsequent employee for
making a similar assignment until all tasks are assigned out?

If I had to summarize our problem in narrative I would do so as follows: We
have a total population of 65 tasks (or schedule assignments). They are
assigned to successive employees without replacement until all 65 tasks are
assigned out. Each successive employee is given an assignment from a
diminishing population of available tasks. Furthermore, the population of
tasks available to any successive employee may be further reduced by the
tasks that they are not authorized to perform. Off course, this means we
may need to process employees with limitations first so that we don't end up
with a residual population that offers no authorized tasks for an employee.

Thanks for your help.

************************

 

[Kyle Kilgore]

I know I am about 2 years late here, lol and the likeliness this example excel worksheet with all the correct coding described previously would still be available is slim to none.

However, I am an optimistic man willing to pay you (if need be) for your previous efforts helping BLUE MAX with his "Random generation of employee assignments"

Would you be able to provide me an excel file of what you have described TO BLUE MAX but allow variables such as "EMPLOYEE"amounts "WORK ASSIGNMENTS" amounts AND "DAYS" amounts to be changed to any number.

I want to be able to allow 'X' DAYS to pass before allowing EMPLOYEE 'X' to be re-assigned ASSIGNMENT 'X'

I have over 30 employees and over 90 work assignments. employees cannot receive the same work assignment within 90 days (3months) you can get me more quickly at
kyle a kilgore at g mail dot com (No spaces)

thanks so much in advance!

We have 65 work assignments and must randomly re-assign them to our
employees each month. We have given each work assignment a number 1-65.
How do we randomly re-shuffle a list of 45 whole numbers at the beginning of
each month?

Once we have learned how to perform the random re-shuffle above, we would
also like to learn how to tailor the random assignments as follows:

First, how do we prevent an employee from being re-assigned the same
assignment they performed last month or perhaps the last 3 months?

Second, how do we prevent employees from receiving certain random numbers
that represent tasks they are not capable of performing? In other words,
how do we prohibit an employee from being assigned a subset of certain tasks
(task numbers) included in the comprehensive list of tasks?

Thank you for any help with this question.

On Sunday, October 04, 2009 3:50 PM JoeU2004 wrote:
I am confused. Do you have the same number of work assignments as employees?
Or do you have 45 employees that you need to assign randomly to 65 work
assignments?

The first (same number) is easy.

Ostensibly, create a column of 65 cells, say A1:A65, with the formula
=RAND(). Then create a column of 65 cells, say B1:B65, with the following
formula in B1 and copy it down:

=RANK(A1,$A$1:$A$65)

Pay close attention to the use of relative and absolute references.

Gotcha!.... The RAND() values will change every time you modify any(!) cell
in the workbook and every time you open the file, normally. Presumably that
is not what you want.

One simple way to avoid that is use the following macro and replace =RAND()
with =myrand():

Function myrand(Optional arg)
Dim first
If first = 0 Then Randomize: first = 1
myrand = Rnd
End Function

The optional arg makes it easy to generate a new set of random values.
Simply use =myrand($C$1). Then, a new set of random values are generated
whenever C1 is changed (e.g. pressing Delete).



Applying these constraints are feasible. But they require, or at least are
best implemented by, a macro. A good design of that macro is non-trivial
and goes beyond the scope of what I can deal with here. My suggestion is
that you higher an experienced Excel/VB programmer to implement it.

FYI, a "bad design" is certainly easier. A "bad" design will iterate the
random selection until the necessary constraints are met. Such a design is
not guaranteed to work in a finite amount of time, and if it does work, it
may or may not take a very long time, depending on stochastic properties.
Caveat emptor!


----- original message -----

On Sunday, October 04, 2009 5:36 PM JoeU2004 wrote:
Sorry, I am not familiar with that.



I provided a solution for exactly what you asked for initially, namely:
"How do we randomly re-shuffle a list of 45 whole numbers [...]?"

But I suspect the following might be more useful.

Suppose you have a column of configurations in A1:A65 and a column of
employees in C1:C65. Fill D165 with the formula =myrand() or =RAND().
Then put the following formula into B1 and copy down through B65:

=INDEX($C$1:$C$65,RANK(D1,$D$1:$D$65))

Again, pay close attention the use of relative and absolute references.

This randomly assigns employees in B1:B65 to respective configurations in
A1:A65.


----- original message -----