Microsoft Excel Duplicate Entries

Robert Ramstetter By Robert Ramstetter, 1st Jan 2015 | Follow this author | RSS Feed | Short URL http://nut.bz/2q9i0qrj/
Posted in Wikinut>Guides>Technology>Computer Software

You have a very extensive spreadsheet and you notice that you have duplicate entries. Fortunately, Microsoft has incorporated a tool that allows you to easily remove them.

My spreadsheet was much larger than expected, then I discovered the reason.

The Microsoft suite of Office products is one of the most versatile products on the market. I am constantly amazed at the depth of functionality that has been built into it. Even if you use any of the Office products on a regular basis and think you know everything, you can still be surprised.
A few days ago, I was working on an Excel spreadsheet that I had pulled using a pre-configured data extraction. I expected to have approximately four hundred records and I was surprised to find over two thousand. I scanned over the document briefly before sorting while I deleted the unneeded columns of information. It was then that the record at the bottom of my screen caught my attention. It was identical to the first record. A subsequent sort of the records confirmed my suspicions. Each record was duplicated anywhere from four to six times. Needless to say, manually deleting the duplicate entries would be extremely time-consuming.
My first course of action was to run the extraction again, taking care this time to check for any settings that I missed. The resulting extraction was identical to the first. My only choice was to deal with the data that I had, somehow. I am a product of the Google generation, so the first thing that I did was to search online for a solution. I immediately found several, and within no time I was able to delete all of the duplicate entries. If you find yourself in a similar situation, follow these steps and you will have the spreadsheet data that you need minus duplicate entries:

Microsoft has included a method for easily deleting the duplicate entries.

In this sample database, you will see that I have several duplicate entries. While it would be easy to manually delete the duplicates from this small database, a much larger one would not be so easy. Fortunately, Microsoft has included a method for easily deleting the duplicate entries regardless of the size of the spreadsheet.

The Data Tab.

First, at the top of the page, select the “Data” tab. Then, click the “Remove Duplicates” option. This will bring up your next dialog box.

Make sure you choose an appropriate column that contains unique data.

In the dialog box that is now open, you can choose one or more columns to sort for duplicate entries. Also, check the box on the right if your document has headers. It is very important that you carefully select the appropriate column to filter and delete duplicate entries. I recommend saving a backup copy first and deleting only one column at a time.
In my case, I was working with student data. My columns were “First Name”, “Last Name”, “Classroom” and “Student ID”. Out of all of the columns, the only one with unique data, where I could safely delete any duplicates, was the “Student ID”. Make sure you choose an appropriate column that contains unique data, or you will lose records that you need. For instance, if I chose “Last Name”, any student with a brother or sister in that school would have been deleted.

The Finished Product.

If you followed the steps and carefully chose the proper column, the duplicate entries in your spreadsheet will be removed and you can continue with your work after saving the document.

Summary

Carefully following a few simple steps will eliminate duplicate entries from your spreadsheet, no matter how many records you have. As best practice, make a copy of your original work before proceeding to have the software automatically delete records. This is sometimes referred to as making a “Chicken Copy”. You do not want to save the finished product over the chicken copy either. There would be no point in making a copy in the first place. You may need to refer to the original spreadsheet if something was inadvertently deleted.

Tags

Duplicate Content, Duplicate Entries, Excel, Microsoft, Spreadsheet

Meet the author

author avatar Robert Ramstetter
Robert Ramstetter is a world traveler and writer of short stories, full length novels, and a vast array of technical articles.

Share this page

moderator Peter B. Giblett moderated this page.
If you have any complaints about this content, please let us know

Comments

author avatar Fern Mc Costigan
1st Jan 2015 (#)

Thanks for sharing such an interesting topic, I like excel and this year will start programing in this program!

Reply to this comment

Add a comment
Username
Can't login?
Password