Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In this worksheet called Remove Duplicates, we've got duplicate records. Row six, row seven, that's Michael Adkins, same Building, same Department, everything is the same, that's a duplicate. We've got two Thomas Allens, but they're in different Departments, so different people there. We need to get rid of duplicates. A list like this doesn't have to be sorted but maybe that's how you've found a few. There might be another one down here somewhere else here; there is another one yeah, 47 and 48, Tim Beasley. But think of how painstakingly boring it's going to be if we start scrolling through this list looking for duplicates.
And the list might be large. And by the way, you can get a quick read on the list if you simply click one of the column entries here. I'm clicking column C. What do we see in the Status Bar? Count 763 simply means we got 763 cells that have data in column C. We're also counting this, so we've got 762 rows below this. The list that we're about to use here does not have to be sorted. Maybe that's how we saw these duplicates, but that's not a prerequisite for using the feature of Removing Duplicates.
It's found on the DATA tab. And as is typical with a lot of database commands in Excel, if we have no empty rows or columns in our list, simply click within the data and then on the DATA tab in the Ribbon use the choice Remove Duplicates. In the Remove Duplicates dialog box we'll see a list of all the columns that had been selected. Now, possibly in some list you don't want to include all columns. I think in this case, we would. We want to say in effect if in every single column here all the way across, the entries are identical then we want to get rid of one of those or in some cases maybe if we've got the record in triplicate, get rid of the duplicates so we have only one of those left.
But do check that out at different times, sometimes you need only to check a few columns. Let's get rid of the duplicate records here, click OK. 21 duplicate values found and removed. That really means duplicate rows and we see only one Michael Adkins, we do however see two Thomas Allens because it's not a duplicate record all the way across. Seven hundred and forty one unique values remain, think of rows. Click OK and our list is all cleaned up. What the feature didn't do and sometimes you might want this but it doesn't happen with this feature, it didn't show us which records were duplicated, it simply got rid of them.
So that's what the feature really is all about, get rid of those duplicate records. It's a common database management problem and this command, Remove Duplicates on the DATA tab simply gets rid of the duplicate records in a list.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 90319 Viewers
80 Video lessons · 137824 Viewers
59 Video lessons · 56550 Viewers
52 Video lessons · 70181 Viewers
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.