I have a solution that should work for you, and implements all the features
you wanted. Hopefully these steps are easy to follow. If not, let me know.

We need to first build the table that holds the tasks the the employees
cannot do. In a new workbook, on Sheet2, enter "EE - Task", "EE ID", and
"Task ID" into A1:C1. In A2, enter the formula "=B2&" - "&C2". In B2 and C2,
enter the Employee unique identifier (ee id, or SSN or possibly name), and a
SINGLE task id that that employee cannot complete. Fill out the rest of the
table by copying down the formula in column A and entering the EE ID and Task
ID. Name this whole table TaskTable (just select the whole table and type
TaskTable in the Name Box).

Now for the task assignment page.
1. On Sheet1, type the following column headers into A1:H1 : Employee, Rand,
New Task, Current Task, Last Task, Prior Task, Is New Task, Can do Task
2. Type "=Rand()" into B2
3. Type "=RANK(B2,$B$2:$B$66)" into C3
4. Type "=AND(C2<D2,C2<E2,C2<F2)" into G2.
5. Type "=ISNA(MATCH(A2&" - "&C2,TaskTable,0))"
6. Copy row 2 down to row 66.
7. Cells A2:A66 need to uniquely identify the Employee. You can use EE IDs
or SSNs or whatever works for you. The values you entered in the TaskTable
must be a subset of these values.
8. Cells D2:F66 track the employees' three months history of task IDs. These
should be integers from 1 to 65.

Now we'll add the row that checks for a valid solution.
9. In A68, type "Found Solution?"
10. In C68, enter the formula "=SUM(C2:C66)=COUNT(C2:C66)*(COUNT(C2:C66)+1)/2"
11. In G68, enter the formula "=AND(G2:G66)"
12. In H68, enter the formula "=AND(H2:H66)"
13. In I68, enter the formula "=AND(C68,G68,H68)"

In order to generate a solution, hit the F9 key (which recaluates the
formulas, generating new random numbers). Watch cell I68. When this cell
shows TRUE, you have a valid solution. You may want to turn on Manual
Calculation so that the workbook doesn't automatically recalculate and reset
your solution.

--
Rob Jordan
Powered by Creative Laziness


"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?

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.