validation column

Hi all,
I have a problem to make validation to prevent duplication value in column.
Suppose I have arrays of data in column A (Employee ID) that contains
values, i want to no duplication value when i entering new data. If i
entered the same value as previous row then error message should be alert.

Any body can help what should the formula function applied. Thanks in
advance.

cheers.


0
bharijan (9)
8/23/2005 6:43:40 AM
excel 39879 articles. 2 followers. Follow

2 Replies
459 Views

Similar Articles

[PageSpeed] 6

Select all of column A. Assuming A1 is active, use Data/Validation, Select 
Custom, enter =COUNTIF(A:A,A1)=1 then put in your error message.
Bob Umlas
Excel MVP

"benny" <bharijan@sampoerna.co.id> wrote in message 
news:uSvii46pFHA.4088@TK2MSFTNGP15.phx.gbl...
> Hi all,
> I have a problem to make validation to prevent duplication value in 
> column.
> Suppose I have arrays of data in column A (Employee ID) that contains
> values, i want to no duplication value when i entering new data. If i
> entered the same value as previous row then error message should be alert.
>
> Any body can help what should the formula function applied. Thanks in
> advance.
>
> cheers.
>
> 


0
8/23/2005 11:27:12 AM
well done, many thanks for your help

best regards

"Bob Umlas" <Excel_Trickster@msn.com> wrote in message
news:eUpZgW9pFHA.2956@TK2MSFTNGP12.phx.gbl...
> Select all of column A. Assuming A1 is active, use Data/Validation, Select
> Custom, enter =COUNTIF(A:A,A1)=1 then put in your error message.
> Bob Umlas
> Excel MVP
>
> "benny" <bharijan@sampoerna.co.id> wrote in message
> news:uSvii46pFHA.4088@TK2MSFTNGP15.phx.gbl...
> > Hi all,
> > I have a problem to make validation to prevent duplication value in
> > column.
> > Suppose I have arrays of data in column A (Employee ID) that contains
> > values, i want to no duplication value when i entering new data. If i
> > entered the same value as previous row then error message should be
alert.
> >
> > Any body can help what should the formula function applied. Thanks in
> > advance.
> >
> > cheers.
> >
> >
>
>


0
bharijan (9)
8/24/2005 1:03:19 AM
Reply:

Similar Artilces:

Make a name = column cell range in table?
I have a table with a header containing names for each column. Somehow I ended up with names defined for each column, but the range doesn't automatically expand to encompass the full column height (when I increase the rows in my table). Is there some means of configuring things so the column names appearing in my table header will automatically be defined? Check out this web page of Debra Dalgleish: http://www.contextures.com/xlNames01.html#Dynamic -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGro...

getting rid of a hidden column behind the front column
I created columns and rows; however, the 4th column has a column that I had created earlier but couldn't find so I created another one.....Now, I'm positive that I have one on top of the other......how can I see the one in the back so that I can get rid of it (delete it, etc.)?.....thank you so very much...... You'll be able to see if there is a hidden column, because the column letters which you can see will not be consecutive, so you would see A B C E F or something similar in the column headings. Select the whole sheet by clicking the square at the top left, abo...

Loop copy/paste for x columns
Hi, I've got a macro that copies formulae in column W, pastes in column X, then copies this and pastes values (it's probably very uncouth however). What I'd like to to is tie this in to a loop so that it repeats along to column CC, without having to copy in the below formulae 58 times. Any help would be appreciated. Columns("W:W").Select Selection.Copy Columns("X:X").Select ActiveSheet.Paste Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ ...

DON'T PASTE IN HIDDEN COLUMNS
IS THERE A WAY THAT I CAN HIDE VARIOUS COLUMNS ON MY WORKSHEET AND PASTE SOMETHING FROM ANOTHER WORKSHEET ONTO ONLY THE UNHIDDEN COLUMNS. THANKS SHERRY Hi Try this Select the range (inclusive of hidden column), click Edit > Goto > Special > Visible cells only (system will select those cells that are not hidden). Then, carry out the usual copy and paste action. >-----Original Message----- >IS THERE A WAY THAT I CAN HIDE VARIOUS COLUMNS ON MY >WORKSHEET AND PASTE SOMETHING FROM ANOTHER WORKSHEET ONTO >ONLY THE UNHIDDEN COLUMNS. > >THANKS SHERRY >. > ...

Msgbox entry validation
The following Macro will not loop more than twice the error entries. Will someone help me correct it to loop indefinely? Thanks Sub Entry_validation() Dim Teststring As String Dim Inputstring As String Cells(1, 1) = "ABC" 'as given filename Cells(2, 1) = "DEF" 'as given filename Cells(3, 1) = "GHI" 'as given filename Cells(4, 1) = "JKL" 'as given filename errorloop: Inputstring = InputBox(prompt:="Enter Filename" & vbLf & "Enter abort to abort en...

Copy a Table to another place with Column Alignment Displaced
I am using Excel 2007 In earlier version of Excel, I used to utilise a special function (with icon of Camera ?) to copy the contents of a range of cells alongwith the formating to another place in the worksheet, in a sort of 'picture' or 'mirror' so that the column alignment is for columns above the range copied is NOT done. This has a special advantage in a complex Form. Otherwise, when I split a range of cell columns, unfortunately the whole of the column from top of the workshee to the bootom gets split, thereby disfiguring the other portion of the form. How t...

Data Validation -> Validation list is larger than the cell width
I have a workbook that I've just added data validation to one cell. FWIW, the data validation is based on a named range. Here's the (perceived) problem. When I've set up data validation in the past, the selection is the same width as the cell. In this case, the list starts almost a full cell width to the left. What am I missing? Thanks, Barb Reinhardt Barb, In Excel2003 I can replicate what you describe under the following conditions: 1. the named range contains entries that are wider than the data validation cell 2. The data validation cell is r...

Text to Columns...
Does anyone know the official function of the menu option called "Text to Columns..." which is found under the "data" option on the menu bar? How does it work? What is it supposed to do? Answers to these questions would be appreciated. Thank you... Craig craig_madrin@goodyear.com It allows you to separate text in a cell or column of cells either by position (fixed) or based on a delimiter, and place the separated data in one or more columns. It can also do some rudimentary data manipulation, including parsing dates, converting numbers to text, text, etc. Th...

Validating against a DTD
Hello All, I am trying to validate an xml file against a DTD. I went through a lot of examples especially: http://www.xmlforasp.net/codebank/util/srcview.aspx?path=../../codebank/System_Xml/XmlValidatingReader/Validator/validator.src&file=validator.cs&font=3 but its not working in my case. First of all, when i try to add my DTD file in XmlSchemaCollection object, it throws an error saying DocType element is expected. I am using NITF DTD for this and there is no DocType element in it. Any help will be appreciated. Thanks. VD You should not add DTDs to XmlSchemaCollection. XmlSchema...

Data Validation
Hi all I want to set data validation on a cell so that it will accept the following (and only the following) text strings: (1) "BR" (literally) or (2) "NT" (literally) or (3) "nA" where n is any positive integral numerical value including zero and A may take any of the values "L", "P", "T", "V" or "Y" or (4) "An" where n is any positive integral numerical value including zero and A may take either of the values "K" or "D". Is this possible, please, and if so how? thanks -- Return e...

Need help copying to column to rows
I need help copying an entire column of information into multiple rows without doing so manually considering that I have over 4000 addresses that need to be arranged into rows. For example: Column A 1. Smith & Sons 2. 223 5th AVE, New York, NY 11234 3. Phone: (212) 758-0718 4. 5. Joe's Auto 6. 556 60th STREET, Brooklyn, NY 11220 7. Phone: (718) 745-0778 I need this to come out like this: ColumnA Column B Column C 1. Smith & Sons 223 5th AVE, New York, NY 11234 Phone: (212) 758-0718 2. Joe's Aut...

Validation dropdown list is not coming visible
Hi, I'm using XP and 2003. I have a problem that I have once found resolution, but now it came again and cannot remember what needs to be done. Here is the problem: There is a column where Validation is specified in right manner with error message in case of error. When selecting the cell - dropdown list is not coming visible. In case I write something wrong it gives error message. If I recall correctly - it was something to do with some kind of list. How can I change this to work properly? I tried to paste new column from another file where the column is working as I want...

reinstall-validation problem
Hardware hit from lightning so new hd and video card and start over. All goes well with install using a slipstreamed XP-Pro SP3 disk but I notice it never asks for the Product Key during install yet it Activates itself during one of the boots and I think little of it until ... During the updates which followed (100+!!!) I notice Windows Defender doesn't seem to run or leave itself installed. As it is running on my laptop figured I just download it. When I run it, it wants to first do the Genuine Advantage bs which I let it, at which time it says "bogus". Ugh, g...

Validation
Julie the same can be achieve without going thru the double drop down boxes. You could do it just using the combobox alone. Thank again Please stay in the ORIGINAL thread. The archives will thank you. -- Don Guillett SalesAid Software donaldb@281.com "JLong" <anonymous@discussions.microsoft.com> wrote in message news:034101c49c3b$889b5020$a501280a@phx.gbl... > Julie the same can be achieve without going thru the > double drop down boxes. You could do it just using the > combobox alone. Thank again ...

Data Validation
I have set up a data form and have data validation rules on the various input cells. I see that there is an option called "Ignore blanks" in the data validation settings, which is supposed to restrict someone from entering past an input cell without entering valid data, if the "Ignore blanks" box is unchecked. I have tried to do this, but it does not seem to work. Please could someone tell me what I am doing wrong? Provide information, or better yet, a screen capture of the Data Validation dialog box with the Setting tab selected (User ALT+PrintScreen to copy the imag...

validating macro
Hi I am sending a spread sheet out to branchs I need them to complete a sheet (common info) before they do anything else on the workbook. I am looking for a macro or code for VBA which who check (common info) see if the cells are complete if not bring up a error screen informing the user to complete the form and then take them straight to the (common info) sheet. and if possible let them go to the tab which they want to go to. Use an extended version of something along these lines: (if there are lots of cells use a 'for row numbers x to y' or 'do until' statment, o...

Reboot Validator
I'm running XP Pro, SP3. Attempting to install a program from Nero. Software keeps failing at startup, despite numerous re-installs. During installs, it keeps attempting to update the 'reboot validator' which it does not seem to be able to do. What is the reboot validator, and how do I update it. Couldn't find anything useful on MS site and Nero Tech support just says its an MS problem. -- Thank you, B. Parker Never heard of it but Nero Support should know about it: Reboot Validator appears to be a Nero component; cf. http://www.brighthub.com/comput...

Conditional validation?
Hello All I have a sheet that is essentially just columns of data (except for the column headings in the first row), and two of the columns are what I would call 'mutually exclusive': i.e. if a value is entered in a row in either column, the user must be prevented from entering any value in the same row of the other column. The two columns already have some basic validation applied (ranges of permissable numerical values), and this validation would need to be applied for any values that are entered. I am sure this can be done, but cannot see how! Hope someone can help. Many thanks L...

Multiple columns
I am trying to make a database for my movie collection. I would really like to be able to have multiple columns of images (DVD box art) to help me make my choices. So far, the closest I've come to having multiple columns is in the print preview mode of a report. I have created a form, but was unable to find a way to make it have multiple columns and scrolling through 1 title per line gets kind of old. Any help creating a multiple column form would be greatly appreciated. We aren't there, and we may not have the same definition of "multiple columns of images" that you have. ...

REPOST: dynamic validation
I think I wasn't clear that I need the Validation option under the Data menu, List, source values. That is what I am trying to fill with this equation. In other words, range1 and range2 are sets cells which contain values. I want those values to be the dropdown options in the cell based on what the previous cell to the left is selected as. So if Range1 is three cells with Tall, Dark, Handsome and Range2 is Sweet, Pretty, Nice, then based on the selection of F or M in A1, I want the choices for B1 to be one of those lists. I know that quotes are required for the IF. My probl...

Excel 2000 vs 2002 Sheet protection & Validation cells
Hello, I have an Excel spreadsheet built on Excel 2000. I have set sheet protection with password, and I have some unprotected cells with drop down menu (validation with 50 choices) Everything works very well. I sent this file to a friend who has Excel 2002. The same "unprotected" validation cells cannot be modified in his version as the message says that these cells are protected What can be done in order to make it work on his version?? I do have some "very hidden" sheet with password protected VBA properties, if that is an issue. Similarly there are some macros o...

Populating multi-column listbox from collection?
Have a collection containing folders and files that I want to put into a 2 column list box using a For Loop. I have done this in Access vba and .Net apps, but I'm going crazy trying to get this to work in excel vba. Can any one show or point me to a code sample demonstrating this, Please? Found lots of samples of setting the list() property to an array, but nothing on doing it row by row with a loop. Thanks in advance for any help -- / Sean the Mc / "I have not failed. I've just found 10,000 ways that won't work." - Thomas Alva Edison (1847-1931) Was given the a...

Why is my tab key moving my cursor from column A to column k?
In all of my excel worksheets, when I push Tab, my cursor goes straight from Column A to Column K or the next column not shown to the right of the sheet. From there, if I hit Shift-Tab It always goes back to column A, even if that's not where I started out. Please help, it's driving me crazy!!! Turn off Lotus 1-2-3 Transition options Tools, Options, Transition (tab), turn off all transition options --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvp...

How to use data validation
[NB: I re-structure my question since people got misled from my previous one] What I really wish to ask is: I wish to use data validation for a particular cell. But it doesn't work if I choose "custom" in the "allow" box. 1) In Data | Validation | Settings Tab | Validation Criteria, there is an "Allow" Box. 2) I chose "custom", there is a formula. 3) I type in some formula, eg: =J3 (Just Reference) =J3-J1 (Reference-type formula) =round(J1) (Function-type formula) None of them can activate the validation fu...

Column comparison
I am trying to compare 2 columns of numbers so that I can identify and delete numbers no longer required. Can anyone help me find a formula for this please? Many thanks DT Chip Pearson has lots of techniques for working with duplicates: http://www.cpearson.com/excel/Duplicates.aspx =isnumber(match(a1,sheet2!a:a,0)) will return True if the value in A1 (of sheet1) matches any value in column A of sheet2. Dave T wrote: > > I am trying to compare 2 columns of numbers so that I can identify and > delete numbers no longer required. Can anyone help me find a formula for > t...