Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Warning while entering duplicate values in a cell
Hi there guys
Was just wondering - is there a way to get a warning message pop up (or any kind of warning) if a value is entered in a cell that already exists in another cell in that column earlier/later? This will enable me to eliminate entering duplicate values in a cell in a particular column... Thanks muchly. Raj |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Warning while entering duplicate values in a cell
You can use Data Validation to prevent entry of duplicate values.
Select your column (I'll use Column A for this example) From the Data Menu, select "Validation" In the Allow field, select "Custom" Enter the formula: =COUNTIF(A:A,A1)=1 You can also set custom messages to display if duplicates are entered. Note, this will only prevent manual entry of data from being duplicated. It will not find existing duplicates or prevent Copy/Pasting duplicate data. Another option would be to use Conditional Formatting to highlight cells that contain duplicates. This would allow you to find existing duplicates and catch Copy/Pasted data. The same formula above could be used there with one little change. =COUNTIF(A:A,A1)1 HTH, Elkar "Raj Mazumdar" wrote: Hi there guys Was just wondering - is there a way to get a warning message pop up (or any kind of warning) if a value is entered in a cell that already exists in another cell in that column earlier/later? This will enable me to eliminate entering duplicate values in a cell in a particular column... Thanks muchly. Raj |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Warning while entering duplicate values in a cell
Thanks Elkar...
"Elkar" wrote: You can use Data Validation to prevent entry of duplicate values. Select your column (I'll use Column A for this example) From the Data Menu, select "Validation" In the Allow field, select "Custom" Enter the formula: =COUNTIF(A:A,A1)=1 You can also set custom messages to display if duplicates are entered. Note, this will only prevent manual entry of data from being duplicated. It will not find existing duplicates or prevent Copy/Pasting duplicate data. Another option would be to use Conditional Formatting to highlight cells that contain duplicates. This would allow you to find existing duplicates and catch Copy/Pasted data. The same formula above could be used there with one little change. =COUNTIF(A:A,A1)1 HTH, Elkar "Raj Mazumdar" wrote: Hi there guys Was just wondering - is there a way to get a warning message pop up (or any kind of warning) if a value is entered in a cell that already exists in another cell in that column earlier/later? This will enable me to eliminate entering duplicate values in a cell in a particular column... Thanks muchly. Raj |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
How do I protect the cell from users entering values in Excel? | Excel Worksheet Functions | |||
How to take a cell that has 3 values and make 2 more new lines | Excel Worksheet Functions | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions |