0

I have a table with a list of tasks that happen on three shifts, and a list of employees that will be assigned to these tasks.

each employee can't be assigned to the same task twice in the same day and can't have two tasks in the same shift.

Employees Table enter image description here

Schedule Table enter image description here

currently, I'm using this function =INDEX(Employees!$A$2:$A$31,RANK(Employees!B2,Employees!$B$2:$B$31)+COUNTIF(Employees!$B$2:Employees!B2,employees!B2)-1)

I use this function in each column separately and change the "-1" in each column to make sure that no employee gets the same task twice.

The problem is as you can see that sometimes it results in #ref error and sometimes the Rand() function results in the same value as in shift 2 Noah is presented twice also I couldn't check if the employee is working that day or not unless I extract the working employees for each day separately which I want to avoid.

Also, I would like to know if there's a way to make sure that each employee get assigned the same amount of tasks that day or that no employee doesn't get assigned a task.

I can't use VBA as I have to use office 365 on the web which uses typescript only in the enterprise edition.

A.O.G
  • 35
  • 8
  • `=IFERROR(INDEX(Employees!$A$2:$A$31,MATCH(SMALL(FILTER(Employees!$B$2:$B$31,Employees!$C$2:$C$31="yes"),ROWS($A$1:$A1)),Employees!$B$2:$B$31,0)),"")` this results in the first, second, etc smallest value where your value in the Saturday column equals `yes`. This would do for the first shift. For the second shift you can add a condition in the filter `Employees$A$2:$A$31<>[cell value to the left]). Hope this helps you in the direction. – P.b Dec 23 '20 at 21:51
  • ".. list of tasks that happen on three shifts, and a list of employees.." which one is the limiting variable.. the number of task or the number task ? ( I mean if the task (per day) number is bigger, will there be employee performing 2,3 tasks or, or the task will be dropped/shifted to the next day? ) – p._phidot_ Dec 26 '20 at 07:11

0 Answers0