duplicate entry

gdspeare

Board Regular
Joined
Oct 8, 2002
Messages
198
i want to create a macro that will alert me if a particular entry has been duplicated. for example, if an invoice number in col A has been entered already, perhaps a message box.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

maxflia10

Well-known Member
Joined
May 20, 2002
Messages
890
A non-macro approach using Data Validation. Highlight the range. Go to Data/Validation/Custom/enter in formula:

=countif(a1:a100,a1)>1 and change error alert to warning or to stop, if you do not want the user to enter data
 

rikrak

Active Member
Joined
Aug 21, 2002
Messages
255
Hi,

The code below goes into Thisworkbook code.
Select the range (any range) in which you want to prevent duplicates and name it "preventduplicates".
<pre>
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'--------------------------------------------------------
'this code prevents entering duplicates in
' userdefined range named: "preventduplicates"
'--------------------------------------------------------
Dim F, Isect, Rng2Check As Range
Dim count As Long
Dim name As name

'check if name "preventduplicates" exists
For Each name In ActiveWorkbook.Names
If UCase(name.name) = "PREVENTDUPLICATES" Then
Set Rng2Check = Range("preventduplicates")
Exit For
End If
Next
Set name = Nothing
If Rng2Check Is Nothing Then Exit Sub
'------------------------------------------------------

If IsError(Target) Then Exit Sub 'don't find errors
If Target.Cells(1) = "" Then Exit Sub 'allow remove contents
'------------------------------------------------------

Application.EnableEvents = False

'check if target intersects with range to check
Set Isect = Application.Intersect(Target, Rng2Check)
If Not Isect Is Nothing Then
If Target.Cells.count > 1 Then
'entering multiple values not allowed
Rng2Check.Select 'hilite checked range
MsgBox "Multiple values of " & "''" & Target.Cells(1).Value & "''" & " may not be entered in this range!", 16, "Duplicate values not allowed."
Application.Undo
Target.Select
Else
'check if targetvalue value already exists
With Rng2Check
Set F = .Find(Target.Cells(1), MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
FirstLocation = F.Address
Set F = .FindNext(F)
If Not F Is Nothing Then Location = F.Address
If Location <> FirstLocation Then
'targetvalue found more than once
Rng2Check.Select 'hilite checked range
MsgBox "The value " & "''" & Target.Value & "''" & " already exists in this range!" & vbCrLf & _
"Please click OK and enter a different value.", 16, "Duplicate values not allowed."
Application.Undo
Target.Select
End If
End With
End If
End If
exitsub:
Set Isect = Nothing
Set Rng2Check = Nothing
Application.EnableEvents = True
End Sub
</pre>
 

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
If you just want to check a range for duplicate entries in ONE cell and not remove them, do this:

Sub dupes()
Set myRange = Range("a1:a10")
For i = 1 To Range("a1:a10").Rows.Count
If WorksheetFunction.CountIf(myRange, Cells(i, 1).Value) > 1 Then
MsgBox Cells(i, 1).Address & " is a duplicate"
End If
Next i
End Sub


Adjust the ranges to suit your needs.
 

Forum statistics

Threads
1,144,444
Messages
5,724,391
Members
422,548
Latest member
Cuprian

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 MrExcel.com.
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 "mrexcel.com".
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
Top