Are you tired of manually assigning tasks to employees in Google Sheets? Do you want to distribute tasks equally and ensure that no employee gets the same task twice? Well, you’re in luck! In this article, I will show you how to use a simple formula to assign employees to tasks in Google Sheets, without the need for any scripts.
Two Different Approaches
Before we dive into the formulas, let’s address two different scenarios: one with a list of unique tasks and another with a list of tasks that may contain duplicates.
Distributing Unique Tasks (Problem #1)
Let’s start with the scenario where you have a list of unique tasks in Column A and a list of employees in Column C.
In cell B2, enter the following formula:
=ArrayFormula({if(len(C2:C),row(A1:A),),C2:C})This formula will add a virtual column next to the employees’ list, containing sequential numbers. In this case, we have four employees, so the numbers will range from 1 to 4.
Next, in cell G2, enter the following formula:
=ArrayFormula(if(len(A2:A),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),))This formula will assign the same numbering against the tasks in Column A. The number 5 in the formula represents the divisor and should be adjusted according to the number of employees you have. For example, if you have three employees, the divisor should be 4.
Finally, in cell B2, enter the final formula:
=ArrayFormula(IFERROR(vlookup(if(len(A2:A),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),),{if(len(C2:C),row(A1:A),),C2:C},2,0)))This formula uses the VLOOKUP function to assign employees to unique tasks. Make sure to adjust the divisor in the formula if needed. Voila! The tasks are distributed evenly among the employees.
Distributing Tasks with Duplicates (Problem #2)
Now, let’s tackle the scenario where your task list contains duplicates. This time, we need some extra steps.
First, in cell B2, enter the same formula used in Problem #1:
=ArrayFormula({if(len(C2:C),row(A1:A),),C2:C})Next, in cell G2, enter the following formula:
=ArrayFormula(if(len(unique(A2:A)),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),))This formula includes the UNIQUE function within the combo to remove numbering against duplicate tasks.
In cell B2, enter the final formula:
=ArrayFormula(IFERROR(vlookup(A2:A,{unique(A2:A),IFERROR(vlookup(if(len(unique(A2:A)),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),),{if(len(C2:C),row(A1:A),),C2:C},2,0))},2,0)))This formula combines the previous formulas and uses another VLOOKUP function to assign employees to tasks with duplicates.
And there you have it! Now you can evenly distribute tasks, even with duplicate entries, in Google Sheets.
If you’re looking for a visual representation of these steps, check out this Example Sheet.
That’s all there is to it! With these formulas, you can easily assign employees to tasks in Google Sheets. Say goodbye to manual assignments and save time with this efficient solution.
So why wait? Give it a try and start streamlining your task assignments today! For more tips and tutorials on Google Sheets, visit Crawlan.com. Happy assigning!
