Algorithm to prevent cherry picking

[johnsigma]

I need some help with coding an algorithm that will be able to decide the order of work. For example:

I have 5 people that review files. But not all files are created equal. Some are easy to review. Some are very hard. The objective to balance the workload such that everyone ends up with a balanced number of hard records. Right now, people are cherry picking the easy records.

There are hundreds of files that come in every month, and the order in which they come in is random.

So I need the system to track who is due to review the next hard file.

I would like to be able to type in the ”File Type” into the first cell and then the next cell would auto-populate the recommended person to take that next file based on it’s difficulty level and who already has done a difficult review.

I know how to do vlookup and such. I just do not know how to teach a cell to assign the record based on the two variables of “difficulty level” and “who is next to get a difficult record”

File Type Difficulty Level*
File Type A 1
File Type B 1
File Type C 2
File Type D 1
File Type E 2
File Type F 1
File Type G 2
File Type H 1


* 1 Easy, 2 Hard


Any help appreciated.

[Logit]

.
The following uses this formula :

{=IFERROR(IF($E$2>=COLUMNS($A$1:A1),INDEX($B$3:$B$102,(ROWS($A$1:A1))*$E$2-($E$2-COLUMNS($A$1:A1))),""),"")}

NOTE: the formula is entered using CTRL / SHIFT / ENTER


Resource : https://www.get-digital-help.com/spl...array-formula/