» Preventing Duplicates While Entering Data
CATEGORY - Excel Editing
VERSION - All Microsoft Excel Versions
1. Select a range of cells, for example, A2:A20.
2. From the Data menu, select Validation.
3. Select the Settings tab.
4. From the Allow dropdown list, select Custom.
5. In the Formula box, enter the following formula:
=COUNTIF($A$2:$A$20,A2)=1
6. Select the Error Alert tab.
7. In the Title box, enter Duplicate Entry.
8. In the Error message box, enter "The value you entered already appears in the list above".
9. Click OK.
Book Store:
Recommended Books:
- The Total Money Makeover. : A Proven Plan for Financial Fitness
- Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
- Keys to Reading an Annual Report (Barron's Business Keys)
- The Analysis and Use of Financial Statements
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
- Accounting the Easy Way
Duplicate data
Gracie Posted on: 31-12-1969
How do you sort the column with the duplicate error message? Can the column be sorted in ascending order? I want all the messages with duplicate to be together and same with the unique messages. I'm not getting that.Tip on Prevending duplicate data entry thru data validation
Jyoti Shekhar Posted on: 31-12-1969
I found this tip very useful and I have shared this with all my colleagues.
Keep up the good work.
I am an excel enthusiast myself and would like to share my knowledge too.
Duplicate data Step 7
Thomas F. Crawford Posted on: 31-12-1969
I am running Excel 2001 for the MAC.
I don't understand what I am supposed to put in the Error message Box. " in the Error msg box enter the value you entered already appears in the list"
Re: Duplicate data step 7
Okra Mendes Posted on: 31-12-1969
You need to type the message that is in the following brackets:
"The value you entered already appears in the list"
This is the message that will pop up when a duplicate is found.
Excel Formula and Function
Excel Date and Time
Excel Macros and VBA
Excel Text, Editing and Format
Excel General
Excel Data
