How can I distribute tasks to assignees with varying capacities

Question

I have list of tasks and I want to automatically assign them between a list of staff members.

Each staff member has a different capacity for how many tasks they can do and I want the automatic allocation of tasks to take into account each staff member's max capacity.

I also want the allocation to take into account the order in which staff members are listed to facilitate task allocation on a first-come-first-served basis.

So far I've been able to evenly distribute the tasks, and where tasks cannot be evenly distributed, have prioritised staff members by order, however, I've not been able to take max capacity into account.

Here is a link to my Google Sheet: https://docs.google.com/spreadsheets/d/1pr87yOdt0MS7rJ09NKqDMjwNi_UGi5reqqLGQjR8TuY/edit?usp=sharing

Note: the cells containing formulas have been highlighted in Cyan for easy reference.

Do you know how to solve this problem?

Answer 1

You can try with this: I first created a kind of "rounds" finding in each number of tasks assigned who would be able to work resulting in a table like this:

Then I wrapped it in a query selecting Col2 (only the names), where they weren't empty, and limiting the amount to the count of tasks in E2:E:

Let me know if this works for you, of this is what you meant. Here you have the formula in F2:

=QUERY(LAMBDA(name,num,qt,MAKEARRAY(qt*MAX(num),2,LAMBDA(r,c,LAMBDA(round,ind,IF(c=1,round,IF(INDEX(num,ind,1)>=round,INDEX(name,ind),"")))(ROUNDUP(r/qt,0),MOD(r-1,qt)+1))))(FILTER(Availability!A2:A,Availability!A2:A<>""),FILTER(Availability!C2:C, Availability!A2:A<>""),COUNTA(FILTER(Availability!A2:A,Availability!A2:A<>""))),"Select Col2 where Col2 <>'' limit "&COUNTA(E2:E))