Assign individuals in set rotation

lex.crl

Hi,

I have a contact list of 20 individuals that need to be assigned to a document review sheet as tasks are added. The goal is to have each individual assigned to 3 tasks and then it would switch to the subsequent person. Additionally, I would need the rotation to start over once we reached the end of the 20 individuals (60 total assignments).

Thank you!

Lucas Rayala

Hi @lex.crl, I would do this with a helper sheet. In that sheet, create a contact column ("Contacts"), then create a column to sequentially order the contacts ("Order1"). So, the rows in Order1 are numbered from 1 to 20, if you have 20 contacts. Make sure you type these -- don't use an autonumber.

In your doc review sheet, add an auto-number column ("Order2").

In your contact assignment column, you're going to have a complicated column formula -- I'm going to break it out piece by piece as I create it.

COUNT({Contacts})*3 

This will supply the total number of entries in 1 "cycle" of reviews. So, if you have 20 contacts with 3 reviews each, you will have 60 reviews before you start the list over.

COUNTIFS([Order2]:[Order2], <=[Order2]@row)   

This will order entries sequentially, independent of what the autonumber is (if a line is deleted, the autonumbers will be off, but the formula won't). Combine the above:

(COUNTIFS([Order2]:[Order2], <=[Order2]@row)) - 
(ROUNDDOWN(COUNTIFS([Order2]:[Order2], <=[Order2]@row)/(COUNT({Contacts})*3))) 
* (COUNT({Contacts})*3)

This complicated formula takes your entry number and starts it over if you've gone through a review cycle. So if you have 20 contacts, who will perform 60 total reviews in 1 cycle, if you are on your 61st review, it will restart the numbering at 1. I'm going to simplify the next equation by just using "<CYCLE EQUATION>" instead of repeating this again -- I'll give you the entire equation at the base.

ROUNDUP(<CYCLE EQUATION> / 3)

This will increment your numbering only after 3 entries. You can see what happens by looking at the first four entries:

ROUNDUP(1/3) = 1

ROUNDUP(2/3) = 1

ROUNDUP(3/3) = 1

ROUNDUP(4/3) = 2

The entire equation will provide you an integer value corresponding to the contact's Order1 value in your reference sheet, repeating that integer value every three rows. You can now do a lookup of your contact using a simple INDEX/COLLECT. I'm going to represent the larger formula with "<BIG FORMULA>" at first:

=INDEX(COLLECT({Contacts}, {Order1}, <BIG FORMULA>),1)

Making the entire formula:

=INDEX(COLLECT({Contacts}, {Order1}, ROUNDUP(((COUNTIFS([Order2]:[Order2], <=[Order2]@row)) - 
(ROUNDDOWN(COUNTIFS([Order2]:[Order2], <=[Order2]@row)/(COUNT({Contacts})*3))) 
* (COUNT({Contacts})*3)) / 3)),1)

The issue you will have is this: because this a column formula, if you add or subtract people from the list, your counts will change and the entire list will reorder itself. If you add new contacts, you may need to consider archiving the old entries.

Good luck!

lex.crl

@Lucas Rayala Thank you so much for the response! After testing this in my sheet I am getting #INVALID VALUE for the last entry of my 60 task rotation each time. For example row 60 and row 120 are returning with #INVALID VALUE. Any suggestions on how to resolve this?

Lucas Rayala

Hi @lex.crl, I think it's something called a fencepost error. What happens when you try this (I added a "+1" after the "ROUNDUP" statement):

=INDEX(COLLECT({Contacts}, {Order1}, ROUNDUP(((COUNTIFS([Order2]:[Order2], <=[Order2]@row)+1) - 
(ROUNDDOWN(COUNTIFS([Order2]:[Order2], <=[Order2]@row)/(COUNT({Contacts})*3))) 
* (COUNT({Contacts})*3)) / 3)),1)

However, I was thinking about this solution the other day and was going to suggest a simpler scenario. Instead of making a list of twenty in your reference sheet, make a list of sixty (each contact repeated 3 times), then repeat that list over and over, and have the Order1 count continue indefinitely. Then just make your formula an INDEX COLLECT:

=INDEX(COLLECT({Contacts}, {Order1}, [Order2]@row),1)

It's not just simpler, it also allows you to change up the order on the referring sheet if you need to. It would require a tiny bit of upkeep to make sure you don't run out of references, but ultimately it would be easier to switch things from an admin side.

lex.crl

Good afternoon @Lucas Rayala! I tried your first suggestion but adding the +1 then skips the first entry of the first contact so they only had 2 reviews. For now, I have implemented the second, simpler, solution and it appears to be working! Thank you :)

lex.crl

@Lucas Rayala after further experimenting I may have to go back to the drawing board. In instances where there is a specific document assigned out of order but to a certain individual the sheet seems to not following the rotation. For example if reviewer #15 gets requested to review a document when it is not their turn, if I try to manually jump ahead to that row in the sheet the rotation isn't seeming to follow.

The original solution appeared to be working great until the fencepost error and then the solution causing the first individuals review to be skipped...