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.
- After B2:B1000 has been selected, these cells background becomes blue. Now, I clicked on Data menu and then on Validation.
- 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.
- 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.
- 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.
By 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!
Rajesh
Link to this pageNo related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
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.
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.