Automatic Task Allocation to users

[ravikiran]

Hi Excel Ninjas,

I need help with a small macro to automatically allocate tasks to users based on the % of allocation. Missing clarity? Let me put down the example:

I have few tasks (with 2 levels of difficulty) that come up everyday (say 20 in this case) and I have 2 or more employees to be allocated for (say 3 in this case). Number of tasks assigned to each employee differs based on his/her workload. I got this as percentage from the "Settings" tab.

Emp1 - 70%
Emp2 - 15%
Emp3 - 15%

The number of employees and the allocation % will be taken from the "Settings" page. I need help with developing logic for a macro that does the allocation automatically.

Exceptions: I can understand that logic doesn't necessarily be impartial all the time. Say there are only 2 tasks and 3 employees, 3rd employee can be ignored for this case.

I would be very grateful if any of you can help me develop this logic.

Thanks in advance,

Regards,
Ravi.

 

[SirJB7]

Hi, ravikiran!
And which is the criteria for assigning tasks to employees regarding task difficulty?
Regards!

 

[ravikiran]

Sir JB7,
Thanks for looking into this.
Sorry for that. I am loading tasks with Level 1 difficulty assign them to users and the load tasks with Level 2 difficulty.

For all level 2 tasks, if there are not enough of them to allocate based on percentage, I need to identify them and allocate 1 after the other. E.g in the above scenario if there are 3 L2 tasks, we can't divide based on 70:15:15 to 3 users, so allocate 1 for each.

Hope I am clear now.

cheers,
Ravi.

 

[SirJB7]

Hi, Ravikiran!
I'm afraid you're not clear. You should specifically explain if there's any relation between level 1 and level 2 tasks (2:1, 3:1, 50%+, anything), and confirm that the assignments should try to balance employee's workload.
Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.
Regards!

 

[ravikiran]

Sir JB7,

Thanks for pointing out the gaps in my communication. I uploaded the sample file with possible 3 scenarios and what the outcome should be like.

Input:

• Number of Employees
• % of Allocation
• Task List in A:A

Outcome:

• There is no relation between Level 1 and Level 2 tasks. I am going to tackle them separately in different spreadsheets using the SAME logic. So that becomes much easy to tackle.
• It is difficult in some scenarios to split the tasks exactly based on the % or allocation (As in the case of Scenario 2). Emp 2 and 3 are supposed to be allocated with 1.5 tasks each, which could not be done. So it is ok to have the extra one added to the 2/3 employee.
• But I am planning to have a note at the end that details what the Calculated allocation is supposed to be and what is the Actual allocation. (This helps me in re-assigning the extra one manually)

Hope I have more clarity this time. Please let me know if there are any more gaps.

And thanks for looking into this.

Regards,
Ravi.

 

[kchiba]

Hi Ravi,

Check the attached, I am not sure if that is what you were after.

Cheers

kanti

 

[SirJB7]

@kchiba
Hi!
Why removing the values at row 4 or changing it values with this:
=CONTARA($B$6:$B$16) -----> in english: =COUNTA($B$6:$B$16)
and use it the formulas at row 22?
Note the inclusion of the next blank row (16) so as to avoid changing formulas when adding new tasks.
Regards!

 

[kchiba]

Hi SirJB7,

I am not sure what the OP wants, but I think the number of tasks and the split of the number of tasks in B6:B16,
will be dependent on the number of tasks, so if I were doing it, I would have B6:Bx built from the number in Row D (D4 for scenario 1). The tasks is not a constant. So B6:B16 will not be constant and can be built from row D

But it all depends on what the OP is after

Ravi,

am attaching version 2 of the file with the allocations calculated in column E

 

[kchiba]

Sorry, here is the file

 

[ravikiran]

Hi Kanti,

Thank you. This helped me and I am trying to build over it. But I am more interested in getting a macro to do this job. I am working on it.

Thanks for all the help.

cheers,
Ravi.

 

[kchiba]

Hi Ravi,

Please let me know if you need more help on this

cheers
kanti

 

[ravikiran]

Hi Kanti,

That would be great. I am just trying to get this done through VBA because the number of tasks vary everyday and I need to copy paste the formulae every often.

In addition to that, the number of employees is also not fixed. It varies. The only step I need to take is to insert the Employee names and the % of allocation in the settings page + the task list in the Main sheet everyday.

Once I run the macro,

1. I need it to consider the Total number of Employees and their % of tasks to be assigned
2. Assign each task with user
3. At the end of the task list, a report to say the Calculated # of tasks for each emp and Actual # of tasks assigned. (Helps me to check if any employee is assigned additional tasks, I'll manually tackle them)

I'll be very glad if you can help me with the Macro.

Thank you very much,

cheers,
Ravi.

 

[jahangeer100]

Hey...
Informative sharing..
Its a great site for getting the information...
Thank you for sharing this great informative stuff here...

 

[Ram Mher]

Hi Kanti
please evalute the following things from below formula:-
=IF($D$22>ROW()-$D$22,$D$18,IF(($D$22+$E$22)>ROW()-$D$22,$E$18,$F$18))

What is calculating by Row ( )
What will be the False value if "D22> row ()-d22" goes false

 

[kchiba]

Hi Ravi,

I have redone the formulas on the file, please study the forulas and let me know if it works for you, we can then progress to how you want to set up the macro. I am not sure what your inputs into the macro will be,