Conditional Formatting Duplicates

[Joanne]

Hi, I have a column containing several thousand VAT numbers and I want to highlight the duplicates. I have tried conditional formatting, but that picks up all those that are TBC or Not Registered, which I don't want highlighted. I can't see how to exclude text (or include numerical values only). I would be grateful for some help on this. Thank you.

[Pecoflyer]

What is TBC and "Not registered". Perhaps post a sample sheet?
If you want to get rid of duplicates you can use the " Remove duplicates" tool under the Data ribbon

[Joanne]

TBC = To be confirmed and Not Registered = No VAT number. I don't need to see these highlighted. I am only trying to quickly identify the acutal numbers that are the same. I don't want to delete the duplicates, just highlight them for reference purposes.

[Pecoflyer]

Please post a sample sheet. I have no idea how to distinguish TBC and "not registered" from other numbers

[Joanne]

Hi Pecoflyer, please find attached a sample of the column I am using

[Pecoflyer]

Try

 =AND(COUNTIF($A$2:$A$143,$A2)>1,AND($A2<>"tbc",$A2<>"not registered"))

as CF after selecting the range

[Joanne]

That works perfectly - thank you for your quick reply.