Data Validation - Preventing duplicate entries across an ent

[mattdilley]

I currently use a worksheet to track containers we have in store. We have 750 of them and we use excel to keep a record of their location.

Occasionaly, duplicate container numbers are entered, and we need to find a way of inforing us when this happens.

The spreadsheet has various numbers entered acorss the entire sheet and is not limited to specific rows of columns.

Any help on this matter would be greatly appreciated.

Many thanks in advance.

 

[sen_edp]

Hi

check Dave Hawley's site
topic : preventing duplicates

http://www.ozgrid.com/Excel/Formulas.htm

hth

 

[Tom Urtis]

A formula solution may not be your best bet, if as you say you have a lot of rows and columns of data that need to be monitored. All those formulas would bog down the file.

You have a couple issues here. First, as a suggestion to tackle the basic problem of disallowing duplicate entries anywhere in the sheet, try this. Right click on your sheet tab, left click on View Code, and paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Application.CountIf(Cells, Target) > 1 Then
MsgBox "The value " & "''" & Target.Value & "''" & " already exists on this sheet !!" & vbCrLf & _
"Please click OK and enter a different value.", 16, "Duplicate values not allowed."
Target.ClearContents
Target.Select
End If
End Sub

Now, 3 points:

(1) This code only prevents someone from manually entering a duplicate value in a single cell. If the duplicate value is returned as a result of a formula, this code does not help you (amended code will; this code here is just a suggested starting point).

(2) This code will not prevent the manual duplicate entry of numerous cells. Example: say you have the value "Tom" in cell A1. If you select the range B1:B10, and type "Tom", and then press Ctrl+Enter, All 10 cells from B1:B10 will display "Tom" without the code disallowing that action (again, amended code could disallow that too).

(3) This code only helps you for the future; it does not check for duplicate values that may currently exist already.

Hope some of this helps you.

 

[rikrak]

Tom,

What if several containers are in/on the same place:

cont1 : area1
cont2 : area1
cont3 : area3
.......

Then your solution will not work.

 

[Tom Urtis]

As I already said, the code would not work in all circumstances, but it's a starting point. I gave 2 examples of how this code would not apply but could be modified (formula returns and multiple Ctrl+Shift entries). Another example would be filldown of a custom list containing the value already present.

I don't understand the meaning of your example regarding "area1", "area1", "area3". The OP was concerned about duplicate container numbers but your post lists all 3 containers with unique names so I don't see the problem. If you have a similar situation as the OP, please explain what you mean regarding your reference to areas and I or someone else may be able to help you.

 

[rikrak]

If only container numbers will be entered it works great.
Area1, .. are the locations where the containers are stored. When they will be entered too, duplicates may be found.

 

[Tom Urtis]

In that case the OP may want to provide information regarding how the location areas are named, and we can modify the code to disregard that text string when evaluating the presence of like values elsewhere. Shouldn't pose a problem once we know the location naming syntax.