Hospital shift managing help needed-How to highlight duplicate cells in consecutive rows

[alkimbeyhan]

Hi, I'm trying to create our shift calendar at the hospital I'm working in and I have a problem
I'm trying to use conditional formatting to highlight duplicate cells in 3 row range.

For example;

A	B	C	D
01/01/17	JOHN	KATE	ALICE
02/01/17	REED	PETER	DONALD
03/01/17	RICHARD	JOHN	CLEMENTINE

<tbody>
</tbody>

I want B1 and C3 highlighted, because "JOHN" is repeated twice in a three day span, even though they are not necessarily in the same column
I want to know if there is a formula that can be used for this purpose
Cheers

 

[Aladin Akyurek]

On 2016 you have the option Highlight Cells Rules | Duplicate Values.

Otherwise, select A2:D4 and invoke the following formula:

=COUNTIFS($A$2:$D$4,A2)>1

then format as desired.

 

[alkimbeyhan]

Hmm...this formula seems to highlight the next cell in the next column and it highlights all the duplicates in the given range.what i want is for it to just highlight cells if they are repeated twice in three days,not if they are just duplicates. so people don't have shifts every other day and they get a chance to rest with a free day in between.

 

[Aladin Akyurek]

Hmm...this formula seems to highlight the next cell in the next column and it highlights all the duplicates in the given range.what i want is for it to just highlight cells if they are repeated twice in three days,not if they are just duplicates. so people don't have shifts every other day and they get a chance to rest with a free day in between.

In E2 control+shift+enter, not just enter, and copy down:

=IF(ISNUMBER(MATCH(3,ABS($A$2:$A$4-$A2)+1,0)),1,0)

In CF invoke:

=AND(COUNTIFS($A$2:$D$4,B2)>1,$E2=1)