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

Assigning or Allocating Tasks

V

vma2084

New Member
Hi All,

I am trying to create a worksheet for Assigning or Allocating Tasks, not sure if it has to be done through vba or formula. Think the latter could work out. The attached file has 2 tables where the 1st Table has the Tasks that need to be assigned to the respective empid from the 2nd Table.

The 1st Table contains the following
Unique ID - Task ID
Age - Age of each Task
Age Group - Age Group of each Task
Group 1 - Task Categorization
Group 2 - Task Categorization
Max Age - Max Age that can be present for that particular Task
Critical - Based on the Max Age and the Age the practicality of the Tasks need to be assigned, AAA being the most critical.
EmpID - The ID's that need to be retrieved from the 2nd Table

The 2nd Table provides the following details
For each EmpID, the list of tasks that they could work on eg: 5101 can work on Tasks A, B , C, and F while 5121 can work on Task D alone.

Now the Criteria is simple that Tasks needs to be assigned based on the tasks each empID is capable of. However the tasks should be distributed equally for each empID.

At present I use the filter option and manually assign tasks. I have also tried to vlookup option by not keeping the range fixed, it works for a few rows and I recreate the vlookup again.

Note the Data does not contain Task E and F as of now.

Could you please provide an idea to proceed or a solution for the same.

Regards,
Vinay
 

Attachments

  • Assigning Tasks.xlsx
    75.7 KB · Views: 4
Last edited:
Hi Vinay ,

When describing a problem , please give only the essential details ; giving more details than are required to understand and / or resolve the problem , only end up confusing.

In your case , can you clarify the following ?

1. Are the tasks to be assigned to the employees based on the table given or on any other factor ?

What I mean is , if an employee is capable of doing a task , should they be assigned to it ? Or will any other factor need to be considered ? If so , what are the factors ?

2. You have mentioned :
the tasks should be distributed equally for each empID.
Click to expand...
Does this mean that the 1796 tasks should be assigned to the 31 employees so that each employee has around 57 / 58 tasks. What is the extent of variation that is allowed ? What I mean is what can be the minimum number of tasks assigned to one employee , and what can be the maximum number of tasks that are assigned to one employee ?

Should there be equality within the task types ? There are in all 465 Task As , 96 Task Bs and 1235 Task Cs. Should there be equality in the task distribution among these types also ?

Narayan
 
Hi Narayan,

Sorry for the confusion

The Second Table if the Answer to this.
For the emplID's mentioned in the table 2, the tasks that they are capable to perform is mentioned in the respective queue columns. Eg: EmpID 5101-5103 are capable to perform the tasks A, B, C and F, whileEmpID of 5111 will capable to perform only Task A
The next criteria is based on the Critical Column mentioned in 1st Table. So the Critical Tasks (AAA first then BBB and last CCC) need to be assigned first.
Lastly the tasks assigned to them should be done equally for their particular group. Where 363 Tasks A need to be done 11 who can do the Tasks A, for which only 10 of them could do Tasks B and C. While there are 18 in total who could 50 of Tasks B, but out of these 18 10 are performing task A so we need to use the rest 8 to perform Tasks B alone.

I have attached a sample file that is how I am working not by manually doing the same. Now when I meant equally, in the Pivot that I have made in the file we see empid 5101-5104have got 1 extra task than the others while 5109-5111 have got 1 less. There is no Maximum Tasks but the Critical ones need to be assigned first and the difference in count of tasks assigned should not be more than the number of employees that can perform that task.

Please let me know if you require any more details. Not sure if I have confused you further.

Regards,
Vinay
 

Attachments

  • Assigning Tasks.xlsx
    83.5 KB · Views: 9
Hi Narayan,

Sorry to bother you, any luck on the method or solution to go ahead on the Assigning or Allocating Tasks

Regards,
Vinay
 
You must log in or register to reply here.
Back
Top