Conditional format duplicates ignore certain criteria

I have conditionally formatted a column of staff names where duplicates are highlighted - this is fine but there are some duplicates that I do not want to include - these all have the same criteria in that they include the suffix (v)

Is there an easy way of doing this?

What criteria have you used in the conditional formatting? If it is a formula then you can modify the formula to ignore (v).

Pete

I've selected the rule type 'Format only unique or duplicate values'

Sure. The column is a list of names with a suffix at the end always in brackets.

Example - starting in column a cell 6 (a6)

SMITH (A)
JONES (O)
DAVIES (V)
(V)
(V)
JONES (O)

What i want to do is highlight JONES (O) duplicates but not (V)

Roger

Okay, cancel the conditional formats that you have, then select the data from A6 downwards, click on Conditional Formatting | New Rule | Use a formula..., and put this formula in the dialogue box:

=AND(COUNTIF(A$6:A6,A6)>1,ISERROR(SEARCH("(v)",A6)))

Click on the formatting button, then the Fill tab and choose your colour (Red), then OK your way out. You should have what you want, as can be seen in the attached file (I've added some more data). Note that only the second and subsequent duplicates are highlighted, which is what I think you have said you wanted.

Hope this helps.

Pete

Thanks Pete. If i wanted to achieve the same result for another column of data - say in c could i use format painter?

You would need to make the column references into absolute, i.e.:

=AND(COUNTIF($A$6:$A6,$A6)>1,ISERROR(SEARCH("(v)",$A6)))

for the existing conditional formatting, and then you could use the Format Painter to apply the same formats to another column.

Hope this helps.

Pete

Pete - Amazing - thanks

Glad to help.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete

Will do - if possible could the format also include the first duplicate?