Join Dennis Taylor for an in-depth discussion in this video Using the Remove Duplicates command, part of Excel 2011 for the Mac: Managing and Analyzing Data.
- We're looking at a worksheet called List With Duplicates, and you might already have seen that it looks as if the information in rows four and five are identical. Certainly the same name, same building, same department, and then we see it's the same ID and phone number and so on all the way across. And, of course, we'd like to get rid of these. Now, typically we don't know how to find these, although we can certainly, by sorting data, usually bring them together. Now, if I go to the bottom of the list here by simply clicking on one of the cells, and then double-clicking the bottom edge, we see there are 762 rows.
Here's another duplicate down here by the way, Edward Hayes. Now, we've located these because we sorted the data and they appear together, but the feature we're about to show you, Remove Duplicates, doesn't necessarily require that these records be next to one another. I'm going to double-click the top edge of the cell to go back up top. And actually before showing you the feature, I'm going to move one of these entries here a few rows away, just to make the point that the data doesn't have to be adjacent, the two sets of data that are identical, don't have to be adjacent to one another.
So, after selecting these cells, I'm going to drag the bottom edge of this downward with the Shift key, just down a few rows, just to point out that the data's not adjacent to the other duplicate. So, all we need to do is select the entire data. Now, let's say beforehand you've made sure there are no empty rows within the data, no empty columns within the data, simply click on any cell in the data and press Command A. Then, on the data tab of the ribbon, here's the choice Remove Duplicates.
And within the Remove Duplicates dialogue box, possibly we will select all the columns, that would make sense. You could possibly say, "Why don't we just select "columns A, B, C, D and E? "That would be enough because everything else..." If those were duplicated, we'd know we got a problem. Let's just do that. But, why not select them in this down here. And notice, ahead of time, we see the entry, "20 Duplicates Found, 742 Unique Values Will Remain." Remember, we did have 762 rows there. Let's Remove Duplicates.
Keep an eye on Juan Bishop in row four, that's going to stay there. There's a Juan Bishop in row eight, that's going to disappear, and there it is. And at the bottom of the list, Row 742, only one Edward Hayes there. So, we've eliminated the duplicates with that feature called Remove Duplicates. It didn't identify which ones were duplicated, many times we don't care, but the feature works pretty smoothly and easily, Remove Duplicates. And it's a recurring problem that many of us have these days as we frequently download data, get data from other sources and we didn't create it, and sometimes data, along the way, gets duplicated, we don't know how necessarily, we don't care, we simply want to get rid of the duplicates, and this is the way we do it.
Remove Duplicates found on the data tab.
- Multiple-key sorting
- Sorting columns left-to-right
- Filtering columns
- Creating a top-ten list with values or percentages
- Using Advanced Filter for complex filters
- Setting up subtotals
- Using the Remove Duplicates command
- Using SUMIF, COUNTIF, and related functions
- Working with the database functions, such as DSUM and DMAX