Duplicates in a list of numbers.

  • Thread starter Thread starter GBL
  • Start date Start date
G

GBL

Hi:
I have a column of prescription numbers (formatted as numbers). In this
column, I wish to check EACH for duplicates. I wish to hi-lite the cells
where duplicates are present.
I can't seem to find a formula and/or function to do this that does not
require a sort or place errors within the cells. I believe this will
require conditional formatting; but presently have a "no joy" situation.
Any ideas??
 
With your data in say A1:A1000, in cell B1 put the following formula and copy
down:-

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

This will flag all Duplicates. You could now Autofilter on Col B on Duplicate,
select what you can see and do Edit / Go To Visible cells, then do Edit / Delete
/ Entire Row.

Had you wanted to see how many times they had been duplicated, then change the
formula to:-

=IF(COUNTIF($A$1:A1,A1)=1,"","Duplicate "&COUNTIF($A$1:A1,A1)-1)

You can still then filter as above but you need to fiilter on Non-Blanks
 
If you do what to hi-lite them then try this, but I like ken's way better,
easier to remove them

With your data in column A, highlight the column and Go to
Format>Conditional Formatting and you will see the "Conditional Formatting"
dialog box.
From the box directly below "Condition 1" select "Formula is".In this
Formula box type this formula:
=COUNTIF(A:A,A1)>1
Click the "Format" button and then select the format you want to use on all
duplicate entries.
When you are happy with the format conditions you have set click "OK".
Now if you already have duplicates within your table or list they will all
appear formatted as you set, or if the range has no entries, try typing in 2
entries the same within your range.

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **
 
Hi:
Just a note of thanks for putting me on the right path. I used the
conditional formatting method as it suited me needs better; but it was
your choice of function that solved my problem.
Worked like a charm --- Thanks a bundle!!
 
Back
Top