# Process Rotation Schedule - Can't be the same person on each day

1. ## Process Rotation Schedule - Can't be the same person on each day

I'm creating a rotating list of names for multiple different tasks. The below formula works as it should but does not take into account the names next to it on other tasks for that day. On the same day I do not want the same people to be on duty. Is there a way to incorporate a Index/Match function into this so it will return a offset value that does not match the other names that are being used from their respective lists?

Main rotation formula which works

=OFFSET('Input Data (For Schedule Prep)'!\$B\$2,MOD((ROW(G3)+J4),COUNTA('Input Data (For Schedule Prep)'!B:B)-1),0)
enter image description here

Second Formula Attempt that works but is finicky/must allow iterations:
=IFNA(IF(OR(C4=I4,B4=O4,B4=U4,B4=AG4,B4=AM4,B4=AS4),INDEX('Input Data (For Schedule Prep)'!\$A\$2:\$A\$20,MATCH(1,(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(OFFSET(C5,7,0)<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(B4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(I4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(O4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(U4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(AG4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(AM4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20)*(AS4<>'Input Data (For Schedule Prep)'!\$A\$2:\$A\$20),0)),B4),B4)

Example.JPG

2. ## Re: Process Rotation Schedule - Can't be the same person on each day

I suggest you to upload a sample file.
Look at the yellow banner at the top of the page for further instructions.

There are currently 1 users browsing this thread. (0 members and 1 guests)