The scenario is like this. In column A I have a long list of tasks. In another column, assume column C, I have the list of available employees with us. How to assign employees to tasks equally in column B. I am talking about equal distribution of random tasks. Can I do this in Google Docs Sheets with a formula, not using any script?

I want to ensure the following in the process.

1. Tasks must be equally assigned to employees.
2. If any task repeats, then that task must be assigned to the employee, who has already been assigned the same task.

How to Assign Employees to Tasks Equally in Google Sheets

I am approaching this problem from two different angles.

1. One with a list of unique tasks (problem # 1).
2. Another with a list of tasks which may contain repeated tasks (problem # 2).

I have two different formulas for the problems. Please note that the formula for the second problem, i.e. duplicate tasks, will work in both the problem 1 and 2.

Here is my example Sheet that contains both the problems and solutions – Example Sheet.

Distributing Unique Tasks in Docs Sheets (Problem # 1)

See the below sample task list in column A. As you can see there are no duplicate tasks in column A. I want to distribute these tasks among the employees in column C.

I want a formula in cell B2. Here I am going to code that in the below steps.

Step # 1:

We have to distribute the task among 4 employees. So first add a virtual column with the employees’ list that contains the sequential numbers 1, 2, 3, and 4.

=ArrayFormula({if(len(C2:C),row(A1:A),),C2:C})

If the number of employees is different in your case, nothing to worry. The formula will adjust the numbering accordingly.

Step # 2:

In this step, I am going to repeat the same numbering (1, 2, 3 and 4) against tasks. See the formula in cell G2, which does this part.

=ArrayFormula(if(len(A2:A),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),))

You can see that I have used the number 5 as the divisor in the MOD formula in the above combo. It comes twice in the combo.

You must change these numbers if your number of employees in column C is different. As per my example, we have 4 employees. So I have used the divisor 5.

If the number of employees is 3, then the divisor will be 4. No doubt if the number of employees is 2, then the divisor will be 3. Hope you could understand how to adjust the divisor.

Now let’s move to the next step.

Step # 3:

Final Formula (Works in Unique Task List Only)

Here I am going to use the above two formulas in Vlookup.

Syntax:

VLOOKUP(search_key, range, index, [is_sorted])

The formula in Step # 1 will be functioning as the ‘range’ in Vlookup and the ‘search key’ is the ‘Step # 2’ formula.

Column 2 is the index column. That means the employee names. Please refer to Step # 1 formula output above.

Here is that final formula (in cell B2) to assign employees to unique tasks in Google Sheets.

=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)))

Please note that when combining formulas you only need to use the ArrayFormula one time. Also, include the IFERROR function to avoid #N/A errors.

Distributing Tasks Contain Duplicates in Docs Sheets (Problem # 2)

This time the tasks contain duplicates. You can see that by referring any of the screenshots below. So obviously the above formula won’t work. But the procedure is almost the same. But some extra steps are required.

Step # 1:

This is the same formula used in the step # 1 in previous example.

=ArrayFormula({if(len(C2:C),row(A1:A),),C2:C})

Step # 2:

Here the formula is slightly different. To remove numbering against the duplicate tasks, I have included the UNIQUE function within the combo.

=ArrayFormula(if(len(unique(A2:A)),filter(mod(row(A1:A),5),mod(row(A1:A),5)<>0),))

Step # 3:

Here is this step, as per the previous example step # 3, we can use the Vlookup formula. Needless to say, the search key is formula 2 (step # 2) and the range is formula 1 (step # 1).

=ArrayFormula(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)))

You can refer the range F2:F in the following image to see what this Vlookup returns.

Step # 4:

Add the unique task lists to the above Vlookup output as the first column (see E2:E below).

=ArrayFormula({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))})

Step # 5:

Final Formula (Works in Unique/Duplicate Task List)

Finally use the above formula as the range in another Vlookup and the task list in column A2:A as the search keys.

This is our final formula in cell B2 to assign employees to tasks equally in Google Sheets.

=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)))

As already said, this formula will work both in unique task list as well as task list contain duplicates.

That’s all about equal distribution of tasks in Google Spreadsheets. Enjoy!