Simple Rota - no adjacent duplicates

[therictus]

Hi All

have been trying to solve this one myself with a lot of googling, looking on forums etc and can't quite find an answer.

I need to put together a rota for a small team operating audio-visual equipment. Some of the team are trained to operate the mixing desk plus microphones, some are only trained to operate microphones. So I have three lists of people and four slots to fill on each date: One person on the Desk, Roving Mic1, Roving Mic2, Rostrum mic adjustment.

So there are five headers on my sheet - DATE, DESK, ROVER1, ROVER2, ROSTRUM
Date is no problem got that set up to fill itself. I can fill the four columns with names from the sheet containing the columns of names, but what I need it to do is check for a duplicate name in the same row (so that one person is not assigned two jobs on the same day), then I want to check the next row for the next event date and ensure that the people used on the previous date are not used again (no problem if they're used on the next one after that).

I'd really like a reasonably easy formula that I can stick in a cell, will call a name from the source name list, then shunt it to the next name in the list and that I can populate the next row cells with that will check if their name was used in any of the cells in the previous row.

Hopefully that makes sense!

Thanks in advance

 

[Yongle]

Not everything you requested, but perhaps helpful

With Headings in A1 to E1, data starting in row2
Excel 2016 (Windows) 32 bit

date​	desk​	rover1​	rover2​	rostrum​	BLANK​	desk​	rover1​	rover2​	rostrum​
01/11/2018​	Peter​	James​	David​	Dan​		-​	-​	-​	-​
02/11/2018​	Peter​	Dan​	Graham​	Vincent​		Peter​	Dan​	-​	-​
03/11/2018​	David​	Dom​	Des​	Ding​		-​	-​	-​	-​
04/11/2018​	Peter​	James​	Graham​	Vincent​		-​	-​	-​	-​
05/11/2018​	John​	Dan​	Dom​	Dom​		-​	-​	Dom​	Dom​

<tbody>
</tbody>

Sheet: Sheet2

<tbody>
</tbody>

AND This formula in G2 copied across to column J and down the rows
=IF(OR(COUNTIF($B1:$E1,B2)>0,COUNTIF($B2:$E2,B2)>1),B2,"-")

OR use conditional formatting to highlight problem cells
Select range B2:E10 \ click on Home Tab \ Conditional Formatting \ Nw Rule \ Use a formula to determine which cell
Formula is
=OR(COUNTIF($B1:$E1,B2)>0,COUNTIF($B2:$E2,B2)>1)

and apply whichever formatting that you prefer

Data Validation
To help select entries in columns B:E create 2 lists and use data validation
List 1 = all the names
List 2 = those trained for both

Column B data validation list is List 2
Columns C:E data validation list is List 1