Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In this course, Dennis Taylor shares easy-to-use database commands and methods for maintaining an Excel database. The course covers sorting, adding subtotals, auto-filtering, and using the Excel Advanced Filter feature and specialized database functions.
Getting rid of duplicate records in a database, or a large list, addresses a problem that occurs frequently in large lists of data. Finally, as of Excel 2007, a new command called Remove Duplicates is now available on the Data tab. In large lists sometimes you don't see duplicates that easily. I happen to see one here in Row 5. That's Juan Bishop. Here is another Juan Bishop here in Row 10. Now, the names being the same alone isn't enough to indicate that the record is duplicated, but a quick look here, I think you can see pretty clearly with Social Security number's and phone number's duplicated. That's a duplicate record.
Now, in prior versions of Excel, you needed to use something called the Advanced Filter. But with this new capability-- and by the way, the data does not have to be in any particular order. It doesn't have to be sorted here. We can remove duplicates relatively quickly. In this list here, I am going to double- click the bottom edge of a cell first of all just to see how far down it goes. It goes to Row 762. Double- click the top edge down here. Let's proceed with Remove Duplicates. As with other commands in the Data tab, as always before proceeding with commands, if you know that your data is altogether with no empty rows or no empty columns, we can simply click on a single cell on our data and start the process.
Remove Duplicates. Now we possibly don't need to check every single column. In fact, you could say here well, as long as the Social Security number and the phone numbers are the same, we know we've got a duplicate. But on the other hand, these are all checked and it's not going to hurt anything here. If one of those is different though, will not have a duplicate record. It will not be deleted. So we had 762 rows. Let's proceed here using all these columns. Click OK. 20 duplicate values.
Now, I would quibble a little bit with the wording there, but we're talking about rows or portions of rows, records perhaps, found and removed. We're down to 741. So we get down to Row 742 because of the titles. You could see only there is a Juan Bishop here. There had been one in Row 10. Not there anymore. Down at the bottom, used to go to Row 762. Now it goes to row 742. So we did eliminate 20 rows here. Excel's new feature called Remove Duplicates is fast and easy. It works ideally with large or small lists to quickly eliminate duplicate information.
Find answers to the most frequently asked questions about Managing and Analyzing Data in Excel 2010.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.