• 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...

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

Work allocation based on skill set and work types

R

roshalphonso

New Member
Hi there,

I would like to have a solution where I would like to have different work types allocated to different resources based on what they are trained for. I have tried different combinations, but not able to get an answer.

Attached is a sample workbook where:
raw data worksheet - has the data that needs to be allocated with a start date. I need work to be allocated which is less than or equal to a particular date, say 08/08/2022
Tasks for allocation - are the tasks only that need to be allocated from the entire laundry list in raw data.
Work allocation - Green cells is the table i need populated with number of tasks that are to be allocated based on their availability (refer column B) and if they are doing batch checking (refer column C). Rows 18 to 26 highlight whether the resource is trained on that work type or not. If they are not trained on that work type, then they are not be allocated cases of that type.

Other criteria:
- Total cases that can be allocated to a resource is 20 (refer sum in column P of WORK ALLOCATION worksheet)
- If a resource is a backup, then we dont give them any work until the above criteria is reached.
- If FULLTIME (not back up) resources end up with more than 20 items, then they are to be equally distributed among the back ups (refer column A)
- If a resource is allcoated for batch checking (refer column C), then that person should get 1 item less to work if they are checking 0-5 batch items, 2 items less if they are batch checking 6-10 items. (Batch item number will be manually input in column O

You will see some formulas that i have tried to research and add up, but they are not working.

Kindly help.
 

Attachments

  • Sample chandoo workbook.xlsx
    129.4 KB · Views: 8
Thanks for your response V. I have amended the formulas in column S of "WORK ALLOCATION" worksheet. Your findings are correct. Basically I would need to allocate 14 work items of 8th Aug and 56 of 9th Aug, which is a total of 70 work items. Attached is the updated spreadsheet for your reference.
 

Attachments

  • Sample chandoo workbook.xlsx
    72 KB · Views: 2
roshalphonso
One basic question:
Why do You would like to ... allocate ... tasks, which someone has already allocated?
... those tasks has already started.
 
Each day work arrives, is scanned and a case is created. A team lead would extract data of all new cases created yesterday and then allocate the ones. So my column S is just extracting the cases which are greater than or equal to a particular date. The tasks have technically started, but they are not allocated for a person to action............So the objective of this whole exercise is to be able to come up with a number that each one should have for the day for each type of work given the criteria in my original listing.
 
roshalphonso
There are few minor details which I've still to 'find out'.
Could Your logic be different?
Here two new samples ... about something different.
You could get a list, who should do which task too.
 

Attachments

  • SCW1.pdf
    42.8 KB · Views: 3
  • SCW2.pdf
    37.8 KB · Views: 3
Last edited:
Thank you for your input. I like the output in SWC1 and SWC2. More so the output on SWC2 is more likely closer to what i want to achieve. Work should be allocated based on their availability for the day and if the number of items allocated to them should be reduced if they are doing somemthing adhoc allocated to them for example batch checking.
 
roshalphonso
... if the number of items allocated to them should be reduced if they are doing somemthing adhoc allocated to them for example batch checking.
Any code or so cannot guess those other duties.
... I added there some ... batch checkings my way ... I didn't get any idea of adhoc-something
 

Attachments

  • SCW1.pdf
    40.3 KB · Views: 1
  • SCW2.pdf
    40.8 KB · Views: 1
Last edited:
Would you be able to share the excel version, for me to be able to check the logic and if it meets requirements. Adhoc work cannot be defined, so at this time, i will just stick to the logic about batch checking. If a person is allocated as a batch checker, then they should only get 2/3rds of the available work.
 
You must log in or register to reply here.
Back
Top