Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column filtering to match identical numbers | Excel Discussion (Misc queries) | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
I need to find odd numbers in a column | Excel Worksheet Functions | |||
How can you find duplicate numbers in a column? | Excel Worksheet Functions | |||
How do you find duplicate values in excel- 2 columns of numbers | Excel Discussion (Misc queries) |