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