You can use the Data, Validation feature, together with the formula that Bomb mentioned.
Select the cells that you want to validate
Point and click on the Data menu and select Validation
Make sure the Settings tab has been selected
Click on the dropdown icon below Allow and scroll down and click on Custom
Click in the formula text box and type the formula that Bomb mentioned (make sure you change the A's to the column you are validating)
Click on OK
Every time you type a duplicate number in one of the validation cells you will get a Microsoft Excel warning dialogue box, where you can retry entering a number that hasn't been repeated. It will not let anybody put a duplicate number in any of the cells.
Incidentally, it pays to put a light colour for the background of those cells, so that you know where they are for the future.
I use colour coding extensively for identifying various types of cells, to make it easier for remembering.
For example, I use light yellow for unprotected cells and light blue for protected cells
You could use light grey for validation cells
If you apply the same concept to every spreadsheet you make, it makes life so much easier for you and for anybody else that may need to do the data input.
Pasting a one at a time or as a block overides the validation - so it wouldn't be suitable.
Data validation is for data input, not for copying and pasting
is it possible to apply some validation so that the same number cannot appear in a column
Its very important that you give us the full story - not a part, because we do not go off on a tangent then.
OK, let's say you have a list of numbers (1, 2, 3, 4, 5) in A1:A5 on Sheet1.
On Sheet2, you have the label "Numbers" in A1, and values in A2:A3 (2 & 4 respectively).
This macro will add 1, 3 & 5 from the Sheet1 list to the Sheet2 list (but it won't add 2 & 4 cos they're already there).
For Each Cell In Range("Sheet1!A1").CurrentRegion
If IsError(Application.Match(Cell, Range("Sheet2!A:A"), 0)) Then
Range("Sheet2!A65536").End(xlUp).Offset(1, 0) = Cell
NB: ISNA Match handling courtesy Dave Peterson, MVP.
I didn't see "one at a time" before posting the code.
You could use code in the module of the sheet you're pasting into* -- something like:
Private Sub Worksheet_Change(ByVal Target As Range)
If Selection.Cells.Count <> 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
If WorksheetFunction.CountIf(Range("A:A"), Target) > 1 Then
Target = ""
MsgBox "Value already exists."
* access it by rightclicking the sheet tab & selecting "View Code"
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!