+ Reply to Thread
Results 1 to 4 of 4

Complex Work distribution

  1. #1
    Registered User
    Join Date
    06-22-2018
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    2

    Complex Work distribution

    Hi,

    I am trying to setup an automated roster for work distribution. I have attached a spreadsheet with a basic template.

    There are 9 employees and 3 groups of jobs.

    First group A is in a sequence 1 to 9. I filled in an example of 4 employees to show how the job is staggered from day to day
    Second group is Job B, where only certain employees do the job (6 out of 9 to be exact)
    Third group is Job C, again only 7 of 9 do the job.

    Distribution among the three groups of jobs is mutually exclusive.

    I have no programming experience. And I have very basic excel formula experience. So I'd appreciate if the solution is based on formulae rather than VBA and Macros, so that I can make minor tweaks if necessary, and also expand it to rest of the year.

    Thanks.
    Attached Files Attached Files
    Last edited by KrishnaFL; 06-22-2018 at 10:06 PM.

  2. #2
    Forum Contributor
    Join Date
    09-23-2007
    Location
    Melbourne, Australia
    Posts
    176

    Re: Complex Work distribution

    Just wanted to confirm with you.
    You have only used 4 employees in your example, but there are 9 employees. So, I'm assuming that it doesn't matter how you distribute
    these 9 employees among all the Job A's for example, as long as they're relatively evenly distributed.

    Second point is, it will be difficult (a little complicated) without a macro. Are you sure you don't want a macro?

    My third question is, can job B and C be conducted by any of the employees (1-9) excluding those on vacation or unable to do certain jobs?
    If this has helped you please set this question to 'SOLVED'.

  3. #3
    Registered User
    Join Date
    06-22-2018
    Location
    Canada
    MS-Off Ver
    2017
    Posts
    2

    Re: Complex Work distribution

    Thanks Danny2000,

    I think Macros should be fine. I will try to learn them.

    All 9 employees will stagger down through Jobs A. i.e. if Empl 1 does Job A1 today, he will do Job A2 tomorrow, and Job A3 day after. It is not random distribution.

    Regarding Job B, specifically Empl 1, 2, and 4 will NOT do the Job.
    Regarding Job C, specifically Empl 2, and 7 will NOT do the job.

    IF an employee is on vacation, he will not be on the roster for any of the jobs. In the spreadsheet I attached previously, I put the vacation schedule in the bottom. There, if any employee is on vacation he will be marked with an X for that day.

    Appreciate your help.
    Last edited by KrishnaFL; 06-23-2018 at 09:46 AM.

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,420

    Re: Complex Work distribution

    The attached copy of the file from post #1 might be used as a template.
    A rotation is set up such that for Job A the name at the bottom of Monday's list (row 6) appears at the top of Tuesday's using: =B6
    The name at the top of Monday's list will appear in the second position on Tuesday using: =B3
    These should fill automatically once the first day (Monday) is filled manually.
    As to Job B the employee that can do the job but is not assigned for Monday is placed in cell B15 and the font is set to white.
    A rotation similar to that of job A is then used.
    Job C is handled in the same manner as job B.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Work Distribution between periods
    By Jdnethaji in forum Excel General
    Replies: 0
    Last Post: 11-03-2014, 10:22 AM
  2. [SOLVED] Work distribution macro
    By N8237 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-11-2014, 11:17 PM
  3. Data Distribution Problem (my Filters don't work either!)
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-13-2013, 04:01 AM
  4. Work Distribution over Weeks
    By Jdnethaji in forum Excel General
    Replies: 4
    Last Post: 01-27-2013, 05:25 AM
  5. work distribution through macros. Simultaneous users
    By megapist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-27-2012, 05:21 AM
  6. Replies: 3
    Last Post: 03-25-2009, 11:04 AM
  7. distribution of work in excel
    By deepika.nowley in forum Excel General
    Replies: 0
    Last Post: 02-20-2008, 02:26 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1