Highlight or Mark any values in a column that are duplicates insidethe column

  • Thread starter Thread starter magmike
  • Start date Start date
M

magmike

In particular, in my "Phone" column (B), I am wanting to be able tell
by looking at the value in the cell, that another cell in the column
has the same value (the same phone number). Conditional formatting I'm
guessing is the tool (I still want rows who's values aren't duplicates
to be visible, so I would prefer not using a filter) - but what would
the formula be - I am lost on this one.

Thanks in advance for you help!

magmike
 
From Chip Pearson comes this tip.

=COUNTIF($B$2:$B$100,B2)>1

The formula above, when used in Conditional Formatting, will
highlight all duplicates in the range B2:B100. That is, if the value
'abc' occurs twice in the list, both instances of 'abc' will be
highlighted


You can use the following formula in Conditional Formatting to
highlight only the first occurrence of an entry in the list. For
example, the first occurrence of 'abc' will be highlighted, but the
second and subsequent occurrences of 'abc' will not be highlighted.

=IF(COUNTIF($B$2:$B$100,B2)=1,FALSE,COUNTIF($B$2:B2,B2)=1)



Gord
 
Back
Top