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

Distribute tasks of employees equally among themselves

Status
Not open for further replies.

Jagbuzz

New Member
Hi guys,

This is my first post here. I'm a big fan of chandoo and have learnt a lot over the years from this site, mostly randomly based on the need.

I have tasks that come everyday that are a direct result of employee actions or other reasons and have to be distributed equally within the team

Requirement
1. Count the number of people who are available and make an active list
2. Count the number of tasks
3. Assign the number of tasks equally
4. Assign the extra tasks randomly within the active people

Criteria
> Tasks need to be assigned only to the Active Employees
> A person cannot do his own task - exception is "Other" which can be done by anyone
> same person should not get the extra task daily (If any)
> One person cannot do the tasks of only one other person (The tasks need to be Random and Distributed)

Work Done So Far (Attached Excel)
* I was able to achieve assigning random tasks to people without assigning their own tasks through if, index formulae and named ranges

Need help with the rest of the process where i'm assigning only to active people randomly and evenly
P.s: I have posted the same question previously here and this is an upgraded version of the same
 

Attachments

  • Assignments.xlsb
    14 KB · Views: 114
@Jagbuzz

Welcome to Chandoo.org forums and thanks for posting your question.

Interesting problem. However, I will say that it is not correct to say that when you have 35 tasks and 4 (out of 5) available employees, everyone should get at least 8. What if the tasks themselves are not uniformly distributed. Let's say John's name is listed in tasks area more often than others names. That means, for more tasks we can't have John as "Assigned to". This will skew the distribution.

Assuming you just want pure random distribution, you can use below approach. It will result in uniform distribution of tasks over time (ie when you add up tasks assigned on each day against employees, after say an year or six months, you will notice that everyone got fair share of tasks)

  1. Create a subset of employees that are active. Call it active.emps (you can automate this thru formulas and named ranges)
  2. For each task in column C (C3:...), generate a subset of active.emps that excludes employee name in C3
  3. Assign the task to a random employee in that sub-set generated in step 2.
Please find attached workbook with a solution for your problem.

All the best.
 

Attachments

  • Assignments.xlsb
    14.4 KB · Views: 205
Thanks for the prompt response @r2c2

For the skewed distribution in the scenario where john has more names the others would get a major share of the same and john would get the other assignments. So essentially the distribution is equal

As mentioned earlier i was able to randomize distribution but i'm looking at assigning equally only to active people.

And the tasks need to be equal (35/4). So it can be either everyone gets 8 tasks and 3 people get one task extra each (or) everyone gets 9 tasks and one person gets 8 tasks.


The tasks need to be uniform on a daily basis rather from a long term perspective
 
Hi All & @r2c2

I have similar query, but i need to alloacte the product name in differnet truck.

Max loading capicity for each truck is 16 and can change

pls advsie how to resolve this query

Thanks..
 

Attachments

  • Allocate Truck Load.xlsx
    10.6 KB · Views: 38
Hi ,

See if this works on your actual data.

Narayan
Hi Narayan

Thanks for your valuable feedback.. when I change the load capacity from 16 to 20 then the value should also change accordingly..

Basically in my WH I have some products and we prepare schedule to load for the goods ready for dispatch and those are marked as "YES"

Each product has qty. that are boxes. so I want excel to check

1. Check the loading capacity of a truck, (ie. total boxes can be loaded in truck.)
2. allocated the product in each column (truck 1, 2,3....) accordingly
3. allocation of products cannot be greater than the MAX qty.it must be closest match.

Awaiting for your feedback.

Regards, Faraz Shaikh
 
Hi ,

Please upload a workbook which has at least 100 rows of data with enough variation that testing can be done thoroughly.

Narayan
 
Hi Narayan

Please find attached is file update with 100 records

also note if example loading capacity of a truck is only 16 box/truck then column next truck 3 update name truck 4.

this will be great help for us to do swift allocation of goods for delivery.

Regards, Faraz Shaikh
 

Attachments

  • Allocate Truck Load.xlsx
    12.3 KB · Views: 29
This file is working fine.. we will test this real scenario. thanks a lot... for your support. I will get back with you when we have multiple destinations.

Regards, Faraz Shaikh
 
I have approximately 100 rows of data or tasks to assign randomly to a total of 11 team members.. How would I go about this?
 
Please need your help for the similar request:
Requirement
1. Count the number of Employee who are available and make a Location list
2. Count the number of tasks
3. Assign the number of tasks equally and randomy
4. Assign less task to priority 1
5. If Tasks and location are same assing 1st then assign other location
For Example: Total Emploees are 40 and Total Tasks are 250

1690568860712.png
 
Status
Not open for further replies.
Back
Top