duplicate entries

[HR Director]

I have a cloumn that you have to put in an EE number, it the useis Vkookup
to find the employee and the propigates the information requested. I need a
way to tell if I enter a duplicate number in the EE number column. any ideas

 

[Eduardo]

Hi,
Check CPearson web

http://www.cpearson.com/excel/Duplicates.aspx

 

[Otto Moehrbach]

Yes. The following little macro will bring up a message box stating that
the entry is a duplicate. Place this macro in the sheet module of your
sheet. To access that module, right-click on the sheet tab and click on
View Code. Paste this macro into the displayed module. "X" out of the
module to return to your sheet. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If Application.CountIf(Range("B:B"), Target.Value) > 1 Then
MsgBox "The entry is a duplicate.", 16, "Duplicate"
End If
End If
End Sub
If you want this macro to clear the duplicate entry, place this line after
the MsgBox line:
Target.ClearContents

 

[JBeaucaire]

I like to do this with Conditional Formatting. If I enter a duplicate
number, the cell lights up in yellow, as does the cell holds the
original number.

Select your entire range to watch. Lets assume it's Column A, only the
first 100 rows. You can expand this as needed.

1. Highlight all 100 rows from A1:A100.
2. Click Format > Conditional Formatting
3. Condition1 = Formula Is: -*=COUNTIF($A$1:$A$100,A1)>1
*-4. Click on _Format_
5. Click on Patterns, and select YELLOW.

Now, any duplicate entries light up to warn you. This will also not
slow down your sheet like some macros would do.

 

[Shane Devenshire]

Hi,

You can use Data, Validation to prevent the entry of duplicates:

You can prevent duplicate entries in a range as follows:

1. Highlight the range, lets say A1:A100
2. Choose the command Data, Validation
3. Under Allow choose Custom
4. Enter the following formula in the Formulas box:
=COUNTIF(A$1:A$100,A1)=1

One thing to keep in mind - if the user copies and pastes data into the
range where the Data Validation is, it is wiped out and anything can be
entered.

If this is helpful, please click the Yes button.

Cheers,
Shane Devenshire

 

[Ashish Mathur]

Hi,

You may view my solution here.

http://office.microsoft.com/en-us/excel/HA012260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com