find duplicate numbers in a column?

  • Thread starter Thread starter JENNYC
  • Start date Start date
J

JENNYC

Hi! i have a spreadsheet that is accessed by 12 different users all day.
in column A we enter stock numbers. Is there a way to have Excel let me
know if there are ever duplicate stock numbers entered? i can insert
new columns to do this...just not sure what formulas i would use to do
that?? any help is greatlly appreciated!!

thanks in advance!
 
If you want to prevent duplicate Stock Numbers from being entered
here's a thought . . .

If Stock Numbers are to be entered in Col_A, beginning in A2:
•Select A2
•Data>Data Validation
-Allow: Custom
-Formula: =COUNTIF(A$2:A2,A2)=1
•Set the error message to something like "Duplicates not allowed"
•Click OK
•Copy Cell A2 down as far as you need

That will test each Stock Number when it is entered and preven
duplicates.

Note: It will not catch a duplicate if it is copy/pasted from anothe
cell.

Is that something you can work with?

Regards,
Ro
 
Depending what you want, there are a couple different options here. If you
want to prevent duplicate entries, you can use Data Validation.

1. Select the column (A for this example)
2. Select "Data"
3. Select "Validation"
4. Where is says "Allow", choose "Custom"
5. Where is says "Formula", enter: =countif(A:A,A1)=1
6. Click "OK"

If you'd rather allow duplicates to be entered, but just highlight where
they are, you can use Conditional Formatting.

1. Select the column (A for this example)
2. Select "Format"
3. Select "Conditional Formatting"
4. Change the first drop-down box to "Formula Is"
5. Enter the formula: =countif(A:A,A1)>1
6. Select the type of formatting you want (i.e. Bold, red letters,
different background, etc...)
7. Click "OK"

HTH,
Elkar
 
Ron, you are a genius! That works almost perfectly ...only hiccup i have
found is if a stock number is entered on line 200 and i try to enter the
same # on line 210, it works...but if i enter it anywhere above that
....like line 150 it does not work!? any way of getting around that??
 
Yes....Use Elkar's version of the validation formula:
=countif(A:A,A1)=1

That should solve that problem.

Just be aware that the validation prevents the second instance of a
Stock Number. Consequently, before you can put a duplicate in its
proper place, you'd need to clear any previous instances.

Regards,
Ron
 
Back
Top