find duplicate numbers in a column?
 

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

find duplicate numbers in a column?
 

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
•DataData 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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=481999

find duplicate numbers in a column?
 

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

find duplicate numbers in a column?
 

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??


--
JENNYC
------------------------------------------------------------------------
JENNYC's Profile: http://www.excelforum.com/member.php...o&userid=19579
View this thread: http://www.excelforum.com/showthread...hreadid=481999

find duplicate numbers in a column?
 

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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=481999

find duplicate numbers in a column?
 

thank you both very much! works perfectly!! :)


--
JENNYC
------------------------------------------------------------------------
JENNYC's Profile: http://www.excelforum.com/member.php...o&userid=19579
View this thread: http://www.excelforum.com/showthread...hreadid=481999