HELP FAST--How to do a text search for a column so that it indicates if it is a duplicate entry.
Posted by Lou21 on August 26, 2001 5:50 PM
I am trying to create either a macro or a function that will indicate to me when I have entered a name in a column twice. Basically I am inputting names (first and last) and when ever I enter the same name I would like excel to indicate that name to me. I tried using MATCH/and a IF statement, but no luck. I need this for work.HELP PLEASE.........
Posted by Damon Ostrander on August 26, 2001 6:17 PM
Hi Lou,
Here is some code that when inserted into the worksheet's event code area does this:
Dim DupCheckOff As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As Variant
Dim LastRow As Integer
LastRow = Cells(32767, Target.Column).End(xlUp).Row
If DupCheckOff Then Exit Sub
For i = 1 To LastRow
If i <> Target.Row Then
If Cells(i, Target.Column).Value = Target.Value Then
Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _
"Click Yes to edit duplicate," & vbCrLf & _
"Click No to allow duplicate entry," & vbCrLf & _
"Click Cancel to cancel entry.", _
vbQuestion + vbYesNoCancel, _
"Duplicate Entry Checker")
If Answer = vbYes Then
Cells(i, Target.Column).Activate
ElseIf Answer = vbCancel Then
DupCheckOff = True
Target.Value = LastValue
DupCheckOff = False
Exit For
Else
Exit Sub
End If
End If
End If
Next i
End Sub
To get to the event code area, right-click on the worksheet's tab and select View Code.
Happy computing.
Damon
Posted by Ivan F Moala on August 26, 2001 9:02 PM
Here is another way to do this besides
Damons
No VBA
Created by David Hager
To apply data validation to a column which allows only unique items to be
entered, highlight that column and select (in Excel 97 and later versions)
Data, Validation from the menu. Choose the custom option and enter the
following formula (for column A):
=COUNTIF($A$1:A1,A1)=1
give credit to David
Ivan
Posted by Lou21 on August 27, 2001 10:25 AM
How would aI get it to work for one column , Column B?
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Lou21 on August 27, 2001 10:57 AM
How would aI get it to work for one column , Column B?
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Damon Ostrander on August 27, 2001 9:03 PM
Hi again Lou,
You probably already know that the code as written works for any column, and checks against all other entries in the column that is being edited. To restrict this routine to just work on column B, simply add the line of code at the very top after the Dim statements:
If Target.Column <> 2 Then Exit Sub
This will cause the routine to exit for any column other than 2. It would only involve slight modifications to this to make the routine work for any column or set of columns on the sheet, excluding all others.
Damon
How would aI get it to work for one column , Column B? If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Lou21 on August 28, 2001 1:47 PM
Hey Damon,
I have one last question how do I get it to ignore the blank cells.
I owe you big time my fiend....
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Lou21 on August 28, 2001 1:54 PM
Hey Damon,
I have one last question how do I get it to ignore the blank cells and compare the active cell I am typing in to the others in that column B.
I owe you big time my friend....
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Damon Ostrander on August 28, 2001 5:20 PM
Hi again Lou,
To get the duplicate checking to ignore blanks, just add the following line of code at the beginning:
If Target.Value = "" Then Exit Sub
Regarding your second question, do you mean that you want to be able to enter information anywhere in the worksheet, but have the entry checked only against duplicates in column B?
Damon
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
Posted by Lou21 on August 29, 2001 6:18 AM
Hey Damon, The second question is I want it to check the cell that I type the word in.
If DupCheckOff Then Exit Sub For i = 1 To LastRow If i <> Target.Row Then If Cells(i, Target.Column).Value = Target.Value Then Answer = MsgBox(Target.Value & " already exists." & vbCrLf & _ "Click Yes to edit duplicate," & vbCrLf & _ "Click No to allow duplicate entry," & vbCrLf & _ "Click Cancel to cancel entry.", _ vbQuestion + vbYesNoCancel, _ "Duplicate Entry Checker") If Answer = vbYes Then Cells(i, Target.Column).Activate ElseIf Answer = vbCancel Then DupCheckOff = True Target.Value = LastValue DupCheckOff = False Exit For Else Exit Sub End If End If End If Next i
