• Random generation of employee assignments

    From Mukayi Mutsago@21:1/5 to Blue Max on Mon Jun 22 09:52:51 2020
    Hello Joe,

    I have a similar problem to what you posted here except that all my employees can do any task. Did you ever got this to work. If so can you send me a copy of how you wrote your excel formulas. In Rob's example I can find out how Sheet1 is linked to
    sheet2. I typed everything as he said but my employees and tasks in sheet2 are not connected to sheet1 at all. Let me know.

    Thanks
    Muk





    On Tuesday, October 6, 2009 at 11:41:23 PM UTC-4, Blue Max wrote:
    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.

    ************************
    "Rob Jordan" <RobJordan@discussions.microsoft.com> wrote in message news:AB9C3C74-77AA-4629-AC73-1FA2284B2A5E@microsoft.com...
    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.


    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Auric__@21:1/5 to All on Tue Jun 23 03:13:20 2020
    Auric__ wrote:

    Mukayi Mutsago wrote:

    I have a similar problem to what you posted here except that all my
    employees can do any task. Did you ever got this to work. If so can you
    send me a copy of how you wrote your excel formulas. In Rob's example I
    can find out how Sheet1 is linked to sheet2. I typed everything as he
    said but my employees and tasks in sheet2 are not connected to sheet1
    at all. Let me know.

    Dude. The original post was 11 years ago.

    On Tuesday, October 6, 2009 at 11:41:23 PM UTC-4, Blue Max wrote:
    ^^^^^^^^^^^^^^^^^^^^^^^^

    Rather than reply to a long-dead thread, why not just, y'know, ask a question? I for one am not will to go dig through the Google archives to
    read the original posts.

    Ah, I see. You did ask. Well, wait for someone to answer, or google it, or figure it out for yourself. Replying after 11 years is silly.

    --
    If you can write out an idea in English,
    you're halfway to writing it in C.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Auric__@21:1/5 to Mukayi Mutsago on Tue Jun 23 03:11:09 2020
    Mukayi Mutsago wrote:

    I have a similar problem to what you posted here except that all my
    employees can do any task. Did you ever got this to work. If so can you
    send me a copy of how you wrote your excel formulas. In Rob's example I
    can find out how Sheet1 is linked to sheet2. I typed everything as he
    said but my employees and tasks in sheet2 are not connected to sheet1 at
    all. Let me know.

    Dude. The original post was 11 years ago.

    On Tuesday, October 6, 2009 at 11:41:23 PM UTC-4, Blue Max wrote:
    ^^^^^^^^^^^^^^^^^^^^^^^^

    Rather than reply to a long-dead thread, why not just, y'know, ask a
    question? I for one am not will to go dig through the Google archives to read the original posts.

    --
    A wise man distrusts his neighbor.
    A wiser man distrusts both his neighbor and himself.
    The wisest man of all distrusts his government.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)