Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 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
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.