Tech Support Guy banner
Status
Not open for further replies.
1 - 14 of 14 Posts
G

·
Discussion Starter · #1 ·
is it possible to apply some validation so that the same number cannot appear in a column
 

·
Registered
Joined
·
2,255 Posts
You can use the Data, Validation feature, together with the formula that Bomb mentioned.

Steps
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.
 

·
Registered
Joined
·
2,255 Posts
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.
 

·
Registered
Joined
·
2,255 Posts
Great minds think alike eh CoachDan LOL :)

I still use that colour type coding for myself, it just makes it easy to identify areas of the spreadsheet, and to me its all about good spreadsheet design.
 
G

·
Discussion Starter · #7 ·
Thats Really great however i am mainly pasting the numbers in. will it still apply the validation
 
G

·
Discussion Starter · #9 ·
im pasting a a number in the cells one at a time and i cannot paste the same number in twice
 

·
Registered
Joined
·
2,255 Posts
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

Quote
is it possible to apply some validation so that the same number cannot appear in a column
Unquote
Its very important that you give us the full story - not a part, because we do not go off on a tangent then.
 

·
Registered
Joined
·
2,255 Posts
Why are you pasting, instead of just typing the info - surely that is quicker!!!

I am intrigued :)
 

·
Registered
Joined
·
8,546 Posts
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).

Sub Paste_New()
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
Else
End If
Next Cell
End Sub

NB: ISNA Match handling courtesy Dave Peterson, MVP.
 

·
Registered
Joined
·
8,546 Posts
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."
End If
End Sub

* access it by rightclicking the sheet tab & selecting "View Code"
 
G

·
Discussion Starter · #14 ·
well i have to type the isbn numbers of books theese are about 12 digits long and its much mush easyier ot copy an isbn and paste it
 
1 - 14 of 14 Posts
Status
Not open for further replies.
Top