Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JENNYC
 
Posts: n/a
Default 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

  #2   Report Post  
Ron Coderre
 
Posts: n/a
Default 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

  #3   Report Post  
Elkar
 
Posts: n/a
Default 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


  #4   Report Post  
JENNYC
 
Posts: n/a
Default 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

  #5   Report Post  
Ron Coderre
 
Posts: n/a
Default 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



  #6   Report Post  
JENNYC
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
column filtering to match identical numbers sammc2 Excel Discussion (Misc queries) 1 July 14th 05 11:59 PM
Return Count for LAST NonBlank Cell in each Row Sam via OfficeKB.com Excel Worksheet Functions 12 April 17th 05 10:36 PM
I need to find odd numbers in a column whouser Excel Worksheet Functions 3 January 27th 05 07:47 PM
How can you find duplicate numbers in a column? Alvin Excel Worksheet Functions 1 January 16th 05 12:24 AM
How do you find duplicate values in excel- 2 columns of numbers rickmanz Excel Discussion (Misc queries) 1 December 16th 04 12:16 AM


All times are GMT +1. The time now is 04:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"