I have a list of addresses in column C for work orders, I'd like them to be highlighted if there are duplicates so we don't forget a work order on each visit. These work orders/results are dynamic and the list will change daily. Can I just do it using a formula or do I need a script?
Add a comment
|
3 Answers
In Format, Conditional formatting... the following should achieve what you request:
where "Custom formula is" contains:
=countif(C:C,C1)>1
and Range may be set to C:C - or adjusted to suit.
If you use this formula:
=countif(A$1:A1;A2)>0
it will output a value indicating duplicates, then you could use conditional formatting for the highlighting
Copy the formula for all B cells that are adjacent to A cells containing data. A TRUE value in the B column tells you that the value in the A column is a duplicate.
-
Where does the OP need to place the formula? How to use the conditional formatting and where to find and use it? Apr 1, 2014 at 19:10
Can the list be sorted by address? If it can, then one option to to add a new column (call it Column E for example). In cell E2, put the formula
=if (e2=e3,"Duplicate","")
Copy that formula down into all the cells in E, and format them as Bold.
Doing this, the first address in any pair of duplicates will have the word beside them.
(It's not quite the same effect you described, but may be good-enough. Also, though conditional highlighting sounds attractive, it could get confusing if you have a lot of duplicates in the list and they are not sorted together.)
answered Apr 2, 2014 at 10:25
