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