Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
Hi,
Check CPearson web http://www.cpearson.com/excel/Duplicates.aspx "HR Director" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
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 "HR Director" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
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. -- JBeaucaire ------------------------------------------------------------------------ JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=45487 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
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 -- "HR Director" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
duplicate entries
Hi,
You may view my solution here. http://office.microsoft.com/en-us/ex...260381033.aspx -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "HR Director" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Duplicate entries | Excel Worksheet Functions | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Duplicate Entries | Excel Discussion (Misc queries) | |||
Getting rid of not quite duplicate entries | Excel Worksheet Functions | |||
Duplicate entries | Excel Worksheet Functions |