find duplicates

[Dylan @ UAFC]

looking for a way to check for
duplicates in a range
and
say column is list of unique phone numbers
is there are function that would display in a corsiponding
cell to let the data entry know if there is the phone
number is bring duplicated
don't care about highlighting anything

 

[xlm]

try data validation

--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[Keith Faulconer]

Don't know if this will solve your problem or not, but there is an easy way
to get rid of duplicate entries. Highlight the range of phone numbers
including the header row, go to Filter, Advanced. There should be a cell
that says to copy to another place, click that and tell it where you want to
put the entries, then there is a box that says "Unique Entries Only" (or
something like that). Click that and then hit OK. The new list will
contain only unique entries.

 

[Chip Pearson]

These formulas assume that your phone numbers are in A1:A10 and the
formulas are entered in B1:B10.

If you want to mark only duplicated items (e.g., if 123 appears three
times, only the second and third instances are marked as duplicates--
the first instance is not marked), enter the following formula in B1
and fill down to B10:

=IF(COUNTIF($A$1:A1,A1)>1,"duplicate","")

If you want to mark all items that have duplicates (e.g,. if 123
appears three times, all three instances are marked as duplicates),
enter the following formula in B1 and fill down to B10:

=IF(COUNTIF($A$1:$A$10,A1)>1,"duplicate","")

With both formulas, duplicates are marked by the word "duplicate" in
column B, or if the value in column A is not a duplicate, the cell in
column B will be empty.

Change the cell references to your needs. Note, though, that you must
include the $ characters as shown above. If you omit the $ characters,
the formulas will not work properly.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Dylan @ UAFC]

I have a few question I am currently using
=if(COUNTIF($b$1:$B$20000,b1)>1,"TRUE","FALSE")

When Copy down FALSE will display until it checks for the dup
how can I get the cell remain blank until the date is entered.
Then If possible how could we have the TRUE render in Green
and the FALSE render in Red.

If you are up for a chalange.
If there any possible way to have the TRUE or FALSE hyperlink
you to the place in the worksheet were the duplicated value exisist

 

[Chip Pearson]

The formulas I posted will display the word "duplicate" if the row is
a duplicate or display nothing at all if the row is not a duplicate.
I'm not sure way you changed it to "TRUE" and "FALSE" if you don't
want to display those values. As an aside, you're better off not using
the quoted string and use just TRUE and FALSE (no quotes).

You can use Conditional Formatting (CF) from the Format menu to set
conditions that when met will change the format of the cell. Select
the cells to which you want to apply the formatting, and open the
Conditional Formatting dialog from the Format menu. There, enter TRUE
in the equal box and then click the Format button and select the font
color and/or the background (pattern) color for cells that contain
TRUE. Then click the Add button to add a new condition, enter FALSE in
the equals box and click Format to specify the format for FALSE cells.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)