Checking if a matching cell already exists

[sharibrand]

I am trying to figure out if there is a way to have excel check if a matching cell already exists, before or at the time that I am entering it.

I am creating a large list of class attendees, and I was hoping to know if the name was already listed before I add them as another record. I know Access can do this type of thing, but I am much more comfortable working in Excel.

Thanks for any help.
Shari

 

[Richard Schollar]

Hi Shari

Welcome to the Board!

You could do this with conditional Formatting: say you are entering your names into column A, select column A (ie click the column letter at the top of the sheet) and go Format>Conditional Formatting. When the dialog opens change to "Formula Is" and use a formula like:

=COUNTIF(A:A,A1)>1

Then clcik the format button, go to the Pattern tab and choose the color you want the cell highlighted if it is a duplicate. Click OK twice.

Now, when you enter a value in column A, if it already exists in column A, the cell will be highlighted with whatever color you chose.

Is this what you wanted?

 

[Kevlarhead]

It's probably a bit inefficient, but I use this to watch for duplicates in my lists.

=IF(COUNTIF(E:E,E4)>1,CONCATENATE(COUNTIF(E:E,E4)," times")," - ")

I'd recommend replacing the open-ended column reference with a named range. It'll return a dash if there are no duplicates, and "n times" if there are n duplicates of the item looked up.

 

[erik.van.geit]

Hi, Shari,
Welcome to the Board !!!!!

try this code, duplicates found in column 1

Private Sub Worksheet_Change(ByVal Target As Range)
'Erik Van Geit
'060218 1720

'if duplicates found the last operation is canceled
'range may contain several areas
'single & multiple cellchanges will be checked
'Ctrl-Enter, copy-paste (values), drag&drop

Dim WatchRange As Range
Dim cell As Range
Dim CellAddress As String

Set WatchRange = Columns(1)
If Intersect(Target, WatchRange) Is Nothing Then Exit Sub

    For Each cell In Target
        On Error Resume Next
        CellAddress = WatchRange.Find(cell, after:=cell, LookIn:=xlValues, lookat:=xlWhole).Address
        On Error GoTo 0
        If CellAddress <> cell.Address And cell <> "" Then
        MsgBox "You entered " & cell.Value & " in " & cell.Address & Chr(10) & _
        "Duplicate found in " & CellAddress, 48, "NO DUPLICATES PLEASE"
            With Application
            .EnableEvents = False
            .Undo
            .EnableEvents = True
            End With
        Exit Sub
        End If
    Next cell

End Sub

http://www.cpearson.com/excel/events.htm
TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

kind regards,
Erik

 

[sharibrand]

Thank you all for your kind assistance and my apologies for inadvertently putting it out there twice.

Happy Friday.
Shari