formula for a duty rota

[nejenkins]

The main spreadsheet has rows for days of the year and columns for duties. There are around 15-20 columns.

Colleagues are allocated duties for that day when their initials are in the cell. One colleague per duty. There are more colleagues than duties so some are not entered each day.

i would like to do a few things

1. highlight where i have put someone on for two duties in a day
2. list which people each day are available (ie. have not be allocated a duty) from a list of initials in some reference cells.

Date role a b c d e f
01/01/2018 NJ EM JS NJ PM KJ
02/01/2018 NJ EM JS NJ PM KJ
03/01/2018 AM EM JS NJ PM KJ
04/01/2018 NJ EM JS NJ PM KJ
05/01/2018 NJ EM JS NJ PM KJ
06/01/2018
07/01/2018
08/01/2018 EM JScr NJ KJ PM
09/01/2018 EH EM JScr NJ KJ PM
10/01/2018 AW EM JScr NJ KJ PM
11/01/2018 EH EM JScr NJ KJ PM
12/01/2018 EH EM JScr NJ KJ PM
13/01/2018
14/01/2018

[Pete_UK]

It would help if you attached a sample Excel workbook, then we can see exactly how your data is laid out.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon (Attachments button), as it doesn't work on this forum.

Hope this helps.

Pete

[nejenkins]

here is sample..........

[JeteMc]

Regarding highlighting where a person has been assigned a second, third etc. duty for a day:
1) Select cells B2:I15
2) Use the following formula as a conditional formatting rule: =AND(B2<>"",COUNTIFS($B2:B2,B2)>1)
3) Choose a format
Regarding persons that have not been assigned, no list (i.e. post #1) is shown in the file.
Let us know if you have any questions.