Data Validation - Preventing duplicate entries across an ent


New Member
Sep 19, 2002
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.

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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."
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.
Upvote 0

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

cont1 : area1
cont2 : area1
cont3 : area3

Then your solution will not work.
Upvote 0
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.
Upvote 0
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.
Upvote 0
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.
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back