Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A not so obvious capability of using the Advanced Filter is the idea of creating a unique list. Now sometimes you might do this even for a single column. There are about 700 or so here. We might want a unique list of the departments. Obviously we see recurring entries here. We might have that need. More expansive is the idea that maybe you got duplicate records in here. There are a couple right here. Do we have others? No we don't know necessarily and if so, where are they? Let's get rid of them.
We are not going to be using the new feature called Remove Duplicates, although that's certainly an option. But let's talk about the idea that we can use the Advanced Filter to create a new list that does not have duplicates. How many records are here? I am going to double-click the bottom edge of this cell. We see that it goes down the row 762. Let's create a new list using the Advanced Filter. We are going to put that list off to the right here. We want to compare its results with what we now have. So the active cell is within our data. Let's go to the Advanced Filter on the data tab and we want to copy the results to another location.
The list range is right here. No criteria range is required. We want unique records only. What's the upper left-hand corner cell? Well we are going to copy these two, let's just scroll over to the right there, and for example M1. That's going to be the upper left hand corner of the receiving area. So we are not in any way altering the existing list; we're about to create a new list consisting of unique records only. Let's click OK and that list is their presumably. Let's go find it. Let's go to the bottom of it too. Double-click the bottom edge.
And this goes down to row 742. So there were some other duplicates in there as well. And you might have seen at the top of the list, there is one Juan Bishop, but Juan Bishop below or above that record. There we see what's happened. So that capability works on the entire list. Now also suggested was this idea. We have got a list of departments here. We just don't happen to have a list of all of our department in one nice place. Let's collect it real fast. This time we are going to use column C only and use the Advanced Filter for just column C. Advanced Filter, adjust this list range to be just column C, copy to another location, no criteria range, Copy to, let's scroll to the right into an empty column.
Simply use Y1 here. Unique records only. Click OK and over in cell Y1 there is that list even with the title. That's a unique list of our departments. No repeats in there. It's like we have got 23 different departments and there they are. You probably want to sort that, but nevertheless there it is, quickly and easily. So the Advanced Filter can be used to create a unique list. In a latter example, simply a unique list based on column entries. Prior to that we saw how it could be used to create a new cleaned up list of this existing data, with no duplicate records.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98511 Viewers
80 Video lessons · 141524 Viewers
59 Video lessons · 59874 Viewers
52 Video lessons · 73071 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.
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.