1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Work Assignment using Excel

Discussion in 'Ask an Excel Question' started by NEWUSER*123, Oct 6, 2017.

  1. NEWUSER*123

    NEWUSER*123 New Member

    Messages:
    3
    Hello,

    I enjoyed the below posting and have a similar problem to solve and would appreciate your assistance. I have attached a sample file for review.
    "Formula Forensics No. 031 – Production Scheduling using Excel"

    I have a list of tasks and a list of technicians
    I need to assign the tasks to technicians.
    If the customer is the same, the task is assigned to the same technician.
    The number of tasks assigned cannot exceed the "Max # Assignments" for the tech.

    I appreciate any assistance you can provide, I am a new excel user.


    Sample Workbook "Tech"
    Technician Max # Assignments
    Michael 9999
    Deondra 9999
    Gary 2

    Sample Workbook "Task"


    TECHNICIANCUSTOMERTASK
    1
    INSTALL PRINTER
    2
    ERROR IN APP
    3
    CAN'T CONNECT
    4
    INSTALL PRINTER
    4
    ERROR IN APP
    5
    ERROR IN APP
    6
    ERROR IN APP
    7
    ERROR IN APP
    8
    UPGRADE MEMORY
    8
    CHANGE SETUP
    8
    ERROR IN APP
    9
    ERROR IN APP
    10
    ERROR IN APP
    11
    CHANGE SETUP
    12
    ERROR IN APP
    13
    CAN'T CONNECT
    14
    INSTALL PRINTER
    14
    ERROR IN APP
    14
    ERROR IN APP
    14
    ERROR IN APP
    15
    ERROR IN APP
    16
    UPGRADE MEMORY
    17
    ERROR IN APP
    18
    CAN'T CONNECT
    18
    INSTALL PRINTER
    19
    ERROR IN APP
    20
    ERROR IN APP
    20
    ERROR IN APP
    21
    ERROR IN APP
    22
    UPGRADE MEMORY​

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    What does it mean when the Maximum number of assignments is 9999 ?

    Surely all the customers cannot be serviced by one technician ?

    List out all possible constraints so that the logic can be incorporated into the scheduling.

    Narayan
  3. NEWUSER*123

    NEWUSER*123 New Member

    Messages:
    3
    Thank you for the response. My apologies for not listing the constraint.
    9999 is the max allowed assignment, the servicing is performed in a round robin rotation and not serviced by one technician.

    Assign the tasks to technicians in a round robin rotation
    example:

    • task 1 is assigned to tech 1, not to exceed "Max # Assignment"
    • task 2 if the customer in task 2= customer in task1, assign to previous tech
    • else, assign to next tech, not to exceed "Max # Assignment"
    • repeat for all tasks
    The count of the Max # Assignment is based on the customer. I uploaded an example of expected results as a visualization.

    I hope this provides clarification and I appreciate your help.

    Attached Files:

  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    See the attached file.

    Narayan

    Attached Files:

    Thomas Kuriakose likes this.
  5. NEWUSER*123

    NEWUSER*123 New Member

    Messages:
    3
    Thank you Narayan,

    The solution is worked great on my production data.

    R

Share This Page