Conditional formatting to highlight duplicate and ignore value x in same column

[Jsejms]

Hello,
I'm trying to figure out how I can highlight cells that contain duplicate values, but only if they are in the same column.

Obviously, I could just go column by column and add a conditional formatting rule for each, but I'd like to be able to use one rule that applies to my entire range of data, and I am not sure how to ignore the exceptions.

Data is in D5:AS53. Here's the rules that need to be considered with the formula/rule:

Highlight duplicate values that:
- Are in the same column

Ignore values that are:
- "RDO", "1", "X"


Anyone know how I can accomplish this? Thank you in advance!

 

[Toadstool]

Hi Jsejms,

Does this do what you want?

Jsejms.xlsx
	D	E	F	G																																				AQ	AR	AS
5	Dog			Goat																																						Cat
6				Goat																																					Sheep
7	Sheep	RDO	Sheep	Goat																																					Sheep	77
32			Sheep	Goat																																				Sheep	1
33		RDO	Sheep	Goat																																					1
34				Goat																																					2
35	X	1		Goat																																					2
51	X	X	Sheep	Goat
52	Sheep			Goat																																						77
53	Pig			Goat																																						Goat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:AS53	Expression	=AND(D5<>"X",D5<>"RDO",D5<>1,COUNTIF(D$5:D$53,D5)>1)	text	NO

 

[Jsejms]

Hi Jsejms,

Does this do what you want?

Jsejms.xlsx
	D	E	F	G																																				AQ	AR	AS
5	Dog			Goat																																						Cat
6				Goat																																					Sheep
7	Sheep	RDO	Sheep	Goat																																					Sheep	77
32			Sheep	Goat																																				Sheep	1
33		RDO	Sheep	Goat																																					1
34				Goat																																					2
35	X	1		Goat																																					2
51	X	X	Sheep	Goat
52	Sheep			Goat																																						77
53	Pig			Goat																																						Goat
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:AS53	Expression	=AND(D5<>"X",D5<>"RDO",D5<>1,COUNTIF(D$5:D$53,D5)>1)	text	NO

That's amazing!! Thanks brother

 

[Toadstool]

That's amazing!! Thanks brother

You're welcome!
Thanks for the feedback.