Need help with finding duplicate entries

[Phil]

Hello,

I have a column that has over 2,500 entries and want to simply highlight it
with ANY duplicate entries found. I have tried this formula
=IF(COUNTIF($D$2:$D$2566, $D$2)>1, TRUE, FALSE which I found on Chip
Pearson's web site (http://www.cpearson.com/excel/duplicat.htm) and it only
highlights the first cell. I KNOW there are more than one duplicates. The
data range begins on column D, row 2, under the col. heading.

Might there a "setting" under my "options" that I need to check on, or
something?

What am I doing wrong?

TIA for your replies.

Phil.

may have numerous en

 

[Gary''s Student]

If you are using the formula for conditional formatting, the dollar signs may
have to be removed from part of the formula to allow it to adjust as you copy
it down the column.

 

[Phil]

Hi,

I don't know what you mean by "as you copy down the column". I am using the
"Conditional Formatting" tool/dialog, which is covered on the web page. Am I
supposed to "copy down the column" instead?

 

[Alan]

The easiest way to do this I think is to use 'Advanced Filter', go for
'Unique Records Only' and put the filtered result in another column. This
will do what it says and remove all duplicates in the new column so you can
compare the two.
Have a look here on Debra Dalgliesh's web site, she is the expert on this
subject and gives clear tutorials on how to use it,
http://www.contextures.com/xladvfilter01.html
Regards,
Alan.

 

[Phil]

Hi Alan,

Unfortunately, I NEED to be able to see ALL of the duplicates in their
native column, because I need to change the values in the next column. So I
don't think that would be the way to go. BTW, I DID get it to work, but
strangely enough, it is not consistent. IOW, in one instance, the col. has 5
KNOWN duplicates, but it only highlighted the last 4, or similarily, it will
highlight the first 4. And sometimes it shows an item highlighted, but there
are NO duplicates. (Fortunately, that doesn't happen alot, or I'd go crazy!)
AND, whether I used this formula =COUNTIF($D$2:$D$2566,D2333)>1 or this one
=IF(COUNTIF($D$2:$D$2566,D2333)>1, TRUE, FALSE) didn't make ANY difference.

What do YOU think?

 

[Phil]

Well, it is working correctly now. I made SURE that my cursor was on the
first cell (which in my case is D2), then I made SURE that ALL of the range
was highlighted. THEN I did the conditional formatting. Now it works great.
Thanks to all who replied.

One MORE question, though. What would be the best way to just show the
duplicates found? IOW, let's say I run the cond. form. and it finds 1200 out
of 3000 records, and highlights them accordingly. Then, how could I just
isolate those 1200 records?

TIA again.

 

[Max]

.. One MORE question, though ..
What would be the best way to just show the
duplicates found? IOW, let's say I run the cond. form.
and it finds 1200 out of 3000 records, and
highlights them accordingly.
Then, how could I just isolate those 1200 records?

You could just try putting essentially the same* CF formula in an empty col
to the right of your data and copy down,
*slightly corrected version from what you posted originally, that is

For example put in say, X2, fill down to last row of data:
=IF(COUNTIF($D$22,D2)>1,TRUE,FALSE)

Then just put a label into X1 and do a:
Data > Filter > Autofilter on col X
and filter out TRUE which would return only the duplicates.
Select all the filtered rows (select the blue-colored row headers) and do a
copy > paste into a new sheet