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.
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.
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!
No 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.