Count Duplicates in a range - skip blanks

  • Thread starter Thread starter jhahes
  • Start date Start date
J

jhahes

What I am trying to do is this

1. Go to column C of sheet1 and highlight from C2: to the last entry in
the column. ex(C35)

2. Name the selected Range "PurchaseOrderNumber"

3. Go to the first empty column - row 2 and put in this formula

=IF(COUNTIF(PurchaseOrderNumber,C2)>1,"Duplicate","")


4. Fill down this in the empty column to the corresponding last entry
in column C (see step1) ex (c35)

5. then have a message box to alert if there are any displays.


******** the only condition is ******
1. in the Column where the purchase order numbers are - some are
blank, some are text, and some are numbers.


I have tried to used the macro recorder for certain parts of the code
but I am stuck now.

I am basically trying to find if there are any duplicate numbers in
this range.
 
Why not use a formula:

=IF(ISNUMBER(C2),IF(COUNTIF($C$2:$C$35,C2)>1,"duplicate",""),"") will check
for numbers only.
 
I like the solution

However

1. there are blanks, text, and numbers - I want the code to skip the
blanks

2. I don't know how many rows or columns of data the sheet will have,
it could change


Thanks
 
It ignores blanks ... why not test it? . and your posting talked about column
C only. Is your column remark relating to the postion of the column where the
formula is to be placed?

Easiest solution is to insert column at beginning of sheet, do run, and then
delete it.
 
Back
Top