#1
|
|||
|
|||
Conditional Formatting Duplicates
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.
|
#2
|
||||
|
||||
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
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
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.
|
#4
|
||||
|
||||
Please post a sample sheet. I have no idea how to distinguish TBC and "not registered" from other numbers
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#5
|
|||
|
|||
Hi Pecoflyer, please find attached a sample of the column I am using
|
#6
|
||||
|
||||
Try
Code:
=AND(COUNTIF($A$2:$A$143,$A2)>1,AND($A2<>"tbc",$A2<>"not registered"))
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
That works perfectly - thank you for your quick reply.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
conditional formatting | otuatail | Excel | 1 | 06-06-2012 05:07 AM |
Using Conditional Formatting to identify duplicates | namedujour | Excel | 3 | 05-24-2012 05:55 PM |
Conditional Formatting. | Laurie B. | Excel | 6 | 04-09-2012 05:01 PM |
Conditional formatting | zanat0s | Excel | 4 | 01-20-2012 03:30 AM |
Conditional formatting with AND, OR | Lucky | Excel | 2 | 10-03-2011 11:41 PM |