Preventing Duplicate records from being entered into a spreadsheet

Posted by Tom Hornstein on February 12, 2002 12:07 PM

I'm entering labeling information into a spreadsheet. Each label has its own row and is sorted by the first row which is the label ID number. The spreadsheet is big and I want to be able to prevent people from entering the same label twice. Does anyone know of a possible solution? Thank you

Posted by Mark W. on February 12, 2002 12:54 PM

See...

22079.html
and think about using Data Validation.

Posted by Damon Ostrander on February 12, 2002 1:10 PM

Hi Tom,

Here's another simple solution: just put the following code into the worksheet's event code module (right-click on worksheet tab, select View Code..., past code in code pane).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
If Target.Column = 1 Then
If Not IsEmpty(Target.Value) Then
LastRow = Cells(32767, Target.Column).End(xlUp).Row
For i = 1 To LastRow
If i &LT;&GT; Target.Row Then
If Cells(i, Target.Column).Value = Target.Value Then
MsgBox Target.Value & " already exists.", vbExclamation
Target.Value = Empty
Exit For
End If
End If
Next i
End If
End If
End Sub

This example just protects against duplicates entered in column 1, but if you want to protect all columns, simply delete the If Target.Column = 1 Then ... End If. Perhaps you can see how to protect any other column or combination of columns from this example.

Enjoy.

Damon

Posted by Tom Hornstein on February 13, 2002 6:58 AM

Re: See...Mark, one more please.

Mark,

Thanks for the advice. It is almost working. I've played with the formula and can't get it perfect. My columns are only A thru H and I'm trying to find the duplicate numbers in column A only. It is doing so but it is only highlighting a some numbers that are not duplicates. Can you offer any other advice please?

Thanks again,
Tom

Posted by Mark W. on February 13, 2002 7:34 AM

Did you decide to use Conditional Formatting rather than Validation? [nt]

Posted by Tom Hornstein on February 13, 2002 9:34 AM

I'm using conditional but am willing to use whatever works.

Posted by Mark W. on February 13, 2002 12:32 PM

Re: I'm using conditional but am willing to use whatever works.

1. Select column A by click on the column header
button.
2. Choose the Format | Conditional Format... menu
command.
3. For Condition1 choose "Formula Is" from the
drop down list.
4. Enter =COUNTIF(A:A,A1)>1 into the Condition1
field.
5. Pick your format after pressing the [ Format... ]
button.
6. Press [ OK ].

That should do it!!!

Posted by Tom Hornstein on February 13, 2002 1:48 PM

Thanks Mark, it worked perfect. I really appreciate your help.