Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,800 courses, including more Business and personalized recommendations.Start Your Free Trial Now
- View Offline
- Multiple key sorting
- Single and multiple column numeric filters
- Creating a top-ten list with values or percentages
- Setting up subtotals
- Creating multiple-field criteria filters
- Creating unique lists from repeating field data
- Using the Remove Duplicates command
- Finding duplicate data with specialized arrays
- Counting the number of unique items in a list
- Using SUMIF and COUNTIF functions
- Working with the database functions such as DSUM and DMAX
Skill Level Appropriate for all
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.
Sign up for a Premium Membership to download courses for Internet-free viewing.
Watch offline with your iOS, Android, or desktop app.Start Your Free Trial
After signing up, download the course here or from the iOS/Android App.