find duplicate numbers in a column?

[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!

[Ron Coderre]

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 prevent duplicates.

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

Is that something you can work with?

Regards,
Ron

[Elkar]

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



"JENNYC" wrote:

>
> 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!
>
>
> --
> JENNYC
> ------------------------------------------------------------------------
> JENNYC's Profile: http://www.excelforum.com/member.php...o&userid=19579
> View this thread: http://www.excelforum.com/showthread...hreadid=481999
>
>

[JENNYC]

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?

...and you are also a genius Elkar! Sorry! I just noticed your advice also! But i still can't figure out what i need to do do make it recognize a duplicate stock number that was entered on a line above the stock number that was entered first.

[Ron Coderre]

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

[JENNYC]

thank you both very much! works perfectly!!