Simple Rota - no adjacent duplicates

therictus

New Member
Joined
Oct 29, 2018
Messages
1
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 :)
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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>
[TABLE="class: grid"]
<tbody>[TR]
[TD]Sheet: Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top