Highlight duplicate value by range in Excel

Question

I have a worksheet:

I need type value into 2 columns: [Supervisor 1] and [Supervisor 2] by condition:

1. Value into [Supervisor 1] column and [Supervisor 2] column must different. If the value is the same so highlight red color (both of them)

Ex:

In D3 cell, I type "John" value. In E3 cell, I type "Susan" value -> No highlight red color

In D3 cell, I type "John" value. In E3 cell, I type "John" value -> Both of D3 and E3 cells highlight red color

In D4 cell, I type "John" value. In E9 cell, I type "John" value -> Both of D3 and E3 cells highlight red color

2. The highlight red color just occur in range on this day

Ex:

In D3 cell, I type "John" value. In E10 cell, I type "John" value -> No highlight red color

In D21 cell, I type "Mary" value. In E13 cell, I type "Mary" value -> No highlight red color

Please help me this problem

--------------------I updated my expect result--------------------

I have a image (my expect result)

In E5 cell, after I type "Louis" value -> highlight red color. Because value in E5 and D4 the same (Note: on this day)

In E7 cell, after I type "Nemo" value -> highlight red color. Because value in E7 and D7 the same (Note: on this day)

In D15 cell, after I type "Messi" value -> highlight red color. Because value in D15 and E12 the same (Note: on this day)

In D15 cell, after I type "Messi" value -> highlight red color. Because value in D15 and E12 the same (Note: on this day)

In D11 cell, after I type "Susan" value -> NOT highlight red color. Because value in D11 and D6 the same but different day exam (Note: on different day)

In E21 cell, after I type "Chen" value -> highlight red color. Because value in E21 and D21 the same (Note: on this day)

Answer 1

If you need to pick up any match between the two columns within the same day, and the day always takes 7 rows, try

=SUM(--ISNUMBER(MATCH($D3:$D9,$E3:$E9,0)))

The formula can be changed if my assumptions aren't correct.

EDIT

If you want to highlight the name in column E if it also appears in column D on the same date, it's just a COUNTIFS

=COUNTIFS(A:A,A3,D:D,E3)

EDIT 2

To highlight the second occurrence only of the name for the same date, you would need two formulae

=COUNTIFS($A$2:$A2,$A3,$D$2:$D2,D3)+COUNTIFS($A$2:$A2,$A3,$E$2:$E2,D3)

for D3:D23

and

=COUNTIFS($A$2:$A3,$A3,$D$2:$D3,E3)+COUNTIFS($A$2:$A2,$A3,$E$2:$E2,E3)

for E3:E23.

Answer 2

I have your solution:

Select the whole range for Column D (looking the picture, I will assume its D3:D23.

In Ribbon, select Home > "Conditional Formatting" > New Rule.

Select Option: "Use a Formula to determine which cells to format". In "Format values where this formula is true" add the following formula: =IF(D3<>0,IF(D3=E3,1,0))

Select the format you want for the cell, clicking in "Format", if D and E are the same and press "Ok".

Now do the same steps for Column E (selecting the whole range for column E), but instead add the following formula: =IF(E3<>0,IF(D3=E3,1,0))

This should definitely solve your issue. If there is a question, please let me know.

Answer 3

This isn't too hard to do... the key insight is that you can use a cell-relative formula within a conditional formatting rule. (You'll need two... one for each cell to check if it matches the other in the pair. If they match, then that triggrs the conditional format.)enter code here