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" wrote in message
...
"Blue Max" wrote:
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" wrote in message
...
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.