Conditional Formatting Issues

[txlonestarr]

Hi All!

I'm working on a large master spreadsheet for work and I'm having an issue with conditional formatting. To summarize, I have two columns I'm working on, adding and removing clients. The first rule is to highlight any duplicate names in the spreadsheet. The second rule is to highlight any names that are "removals". The issue I'm having is that I want to NOT have the duplicate highlight if the duplicate is a removal.

Hopefully that makes sense! I'd be very helpful for any advice you're able to give!!

Thank you!

 

[Ottsel]

Are you able to supply a visual? Otherwise, assuming its column A that you're working in; enter the formula:

=COUNTIF($A$1:$A$100, A1)>1

(change $A$1:$A$100 to match your range) in the "Conditional Formatting" section after selecting "New Rule". Just choose whatever highlight you like.

 

[Peter_SSs]

Welcome to the MrExcel board!

If your question remains unresolved after these first two suggestions, please provide a small set of dummy sample data (preferably with XL2BB so we can easily copy for testing) and explain what should be highlighted and why in relation to that sample data.

Is this what you are after?

23 08 23.xlsm
	A	B
1	Name	Status
2	Name 1	Removal
3	Name 2	Keep
4	Name 3	Keep
5	Name 4	Keep
6	Name 1
7	Name 2	Keep
8	Name 1
9	Name 2	Unknown
10	Name 3
11	Name 7	Keep
12	Name 3	Keep
13	Name 4	Removal
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A13	Expression	=AND(COUNTIF(A$2:A$13,A2)>1,COUNTIFS(A$2:A$13,A2,B$2:B$13,"Removal")=0)	text	NO

 

[txlonestarr]

Thanks for your replies! Those two formulas unfortunately did not work for what I needed them to. I am unable to upload to XL2BB but I have added a couple sample screenshots to show the data I am working with, and the conditional formatting formulas I currently have as well.

In the screenshot of the data, you can see that the 'remove' numbers are in red and the duplicates are in yellow. What I am trying to do is to only show yellow duplicates to numbers that are not removals. I only want duplicates to shown if there are two 'add's' in the data with the same numbers.

Hopefully this helps to clarify a little bit further.

thanks again

 

[txlonestarr]

Disregard my previous reply. I had a few formulas that were blocking the solution formula! Thanks so much for your help!

 

[Peter_SSs]

You're welcome. Glad you got it sorted. Thanks for the follow-up.