Are You Leaving Your Work Tracks On Your Windows PC? Yes! It Can Be Dangerous. Why Not Remove Them?
Powered by MaxBlogPress  

How To Stop Feeding Of Duplicate Entries in Excel

Having the same record twice or more time adds to confusion! If you have hundreds of records on your database, then checking them manually for avoiding duplicate entries is too much time consuming. Right? So, I am writing a tip to help you in automatically preventing you from adding duplicate records in Excel 2000. The same tip should work in other versions of Microsoft Excel too. Let me know if there is some problem in using the same tip in other Excel versions.

I wrote this tip as one of my friends who run a tutor agency wanted to enter details of the tutors and clients in an Excel file and he wanted no too same phone numbers to be entered again. That is, he was assuming that no two different persons would have the same phone number. He had all the tutor details written on register.

The simple way to avoid entering the same phone number (record) in Excel 2000 is explained next:

- Launch Excel (Now I assume that you know how to launch Excel 2000. Use shortcut or type excel in Run Or use Programs menu etc.)

- After Excel program has launched and you see a spreadsheet with lot of blank cells. Choose the cells where you want to stop entering duplicate records. In my case, I selected B2:B1000 (Column B, row 2 to 1000). So, I am going to write the steps considering cells B2:B1000 being selected. Kindly change the formula for avoiding duplicate entries as per cells selected at your end.

Selecting B2 cell to B1000 cells for entering a formula How To Stop Feeding Of Duplicate Entries in Excel

- After B2:B1000 has been selected, these cells background becomes blue. Now, I clicked on Data menu and then on Validation.

Location Of Validation In Data Menu Of Excel 2000 How To Stop Feeding Of Duplicate Entries in Excel

- In Settings tab of Data Validation window, I selected Allow as “Custom” and Formula as “=COUNTIF($B$2:$B$1000,B2)=1″ (without quotes) under Validation Criteria.

Configuring Data Validation Settings For Stopping Duplicate Entries In Excel2000 How To Stop Feeding Of Duplicate Entries in Excel- I clicked on third tab of Data Validation menu, i.e., Error Alert where I entered the error message that I wanted to get displayed when someone enters the duplicate record, i.e., already existing Phone number in this case.

Setting the Error Alert message On Duplicate Entry In Data Validation Menu Of Excel2000 How To Stop Feeding Of Duplicate Entries in Excel- After entering the messages in “Error Alert”, I clicked on OK of the Data Validation window.

Now, I tried entering the same phone number in the second field from B3 to B1000 cells after entering the phone number in B2 cell, I got the error message set by me in Error Alert.

Excel Duplicate Data Validation Error Display On Addition of Same Phone number How To Stop Feeding Of Duplicate Entries in ExcelBy using the above explained logic of the COUNTIF formula, i.e, =COUNTIF($B$2:$B$1000,B2)=1 you can set other cells also from getting the duplicate data entered in either of them.

Example – To avoid entering the same name in cells A2 to A1000, I would first select the cells A2 to A1000 and then enter =COUNTIF($A$2:$A$1000,A2)=1 in Data Validation.

Hope that this helps some of my friends in setting up Excel spreadsheets with prevention set in the cells for non duplicate entries through the use of COUNTIF formula.

Let me know your comments!


Copy the code below to your web site.

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.

Tags: automated unique data, cell data integrity, COUNTIF usage to prevent duplicate records, Data validation, Excel 2000 record management, stop same records entry

This entry was posted on Thursday, June 18th, 2009 at 11:27 pm and is filed under Application SW, MS Excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “How To Stop Feeding Of Duplicate Entries in Excel”

How To Stop Feeding Of Duplicate Entries in Excel | PC tips and tricks | June 20th, 2009 at 10:12 pm

[...] Read the example here: How To Stop Feeding Of Duplicate Entries in Excel | PC tips and tricks [...]

ASAD June 21st, 2009 at 1:35 pm

Dear Rajesh

Thanks for this informative post. One problem with this what I observed is if I manually key in the data (duplicate the data),the error message is prompted. But if I copy and paste or drag the data from the first cell the error message is not being prompted.
Let me know some alternative suggestions.

rajeshmago June 22nd, 2009 at 9:55 am

@ASAD: Yes, you are right. The COUNTIF formula used in my post is not preventing copy paste as well as drag of the same data, i.e., duplicate entry. Request you to refer to this post where Vasant’s Code is being used for preventing duplicate entries in A column by manual entry, drag and copy and paste. I checked and found it working. The URL is: Use the code given by Max (second one from the top, i.e., without any greater than signs). You can modify the code for using it at other columns too. If there are issues, let me know.

Leave a Reply

Switch to our mobile site