Checking if a matching cell already exists

sharibrand

New Member
Joined
Oct 23, 2006
Messages
3
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
=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

Board Regular
Joined
May 23, 2006
Messages
176
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi, Shari,
Welcome to the Board !!!!!

try this code, duplicates found in column 1
Code:
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

New Member
Joined
Oct 23, 2006
Messages
3
Thank you all for your kind assistance and my apologies for inadvertently putting it out there twice.

Happy Friday.
Shari
 

Watch MrExcel Video

Forum statistics

Threads
1,114,384
Messages
5,547,621
Members
410,804
Latest member
bluepinky
Top