Conditional Formatting - with exceptions

Question

	Rohn	Winder	Todd	Smith	Jones	Paul	TOTAL
9/1/2018	D	M	M	D	X	X	4
9/2/2018	M	M	D	D	X	A	5
9/3/2018	A	A	X	D	A	D	5
9/4/2018	L	X	X	D	A	M	4

I need to highlight the duplicates for each day, BUT, I cannot include the "X" as duplicate. 

In the table above, on 9/1/18, I need the two D's and the two M's highlighted, but I cannot have the two X's highlighted.  Is there a way to do conditional formatting (show duplicates) without including ALL of the duplicates?  For further explanation, on the date of 9/4/18 in this example, I would not have anything highlighted .

I'm using Excel 2013--THANKS!!!

Answer 1

Hi Jan,

	Rohn	Winder	Todd	Smith	Jones	Paul	TOTAL
9/1/2018	D	M	M	D	X	X	4
9/2/2018	M	M	D	D	X	A	5
9/3/2018	A	A	X	D	A	D	5
9/4/2018	L	X	X	D	A	M	4

I need to highlight the duplicates for each day, BUT, I cannot include the "X" as duplicate. 

In the table above, on 9/1/18, I need the two D's and the two M's highlighted, but I cannot have the two X's highlighted.  Is there a way to do conditional formatting (show duplicates) without including ALL of the duplicates?  For further explanation, on the date of 9/4/18 in this example, I would not have anything highlighted .

I'm using Excel 2013--THANKS!!!

Select the range B2:Hn and apply conditional formatting using the CF formula:

=AND(B2<>"X", COUNTIF($B2:$H3,B2)>1)

   

===

Regards,

Norman

Answer 2

Hi Jan,

This works PERFECTLY!  Thank you SO much!

You are most welcome! Thank you for your kind feedback.

===

Regards,

Norman

Answer 3

This works PERFECTLY!  Thank you SO much!