Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight or Mark any values in a column that are duplicates insidethe column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight or Mark any values in a column that are duplicates inside the column
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:B 2,B2)=1) Gord On Thu, 26 Jan 2012 17:26:53 -0800 (PST), magmike wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conditional formatting formula to highlight items on column B inrelation to column A | Excel Discussion (Misc queries) | |||
Filter duplicates based on criteria / column values | Excel Discussion (Misc queries) | |||
Automatically highlight max and min values in a column in Excel | Excel Discussion (Misc queries) | |||
Coloring duplicates based on values in a column. | Excel Programming | |||
How do I set up a column in Excel as a check mark column? | Excel Discussion (Misc queries) |