There is a powerful and efficient filtering tool in Excel called Advanced Filter. In addition to filtering values, it has the option of generating a list of unique values. There is one pitfall to avoid when using this in a macro.

Advanced Filter Example

To use the Advanced Filter you need a database (i.e. a list or range within Excel having your data), a set of criteria for your advanced filter (see below) and possibly some column headings from your database which you want to extract your filtered data to.

The easiest way to see what is happening is to see an example.

I have already set up a database, my filter criteria and the columns I want in my output.

Select the Advanced Filter button on the Data tab of the ribbon.

The Advanced Filter dialog displays.

1. There are two radio buttons at the top which determine whether you filter the data in its current location, like what you do with a standard filter, or whether you move the filtered data to another location. If you select the second option to move the data to a new location then the Copy to: field is selectable. In the example above I have selected the second option.
2. In the List range: field select your database, i.e. the range of cells that contains your data.
3. In the Criteria range: field select your range of cells that contain your filter criteria.  I will discuss this in more detail below.
4. If you have selected the second radio button in step 1, in the Copy to: field select the range of cells that contains your heading.  This should not be within your database itself; it needs to be separate to it.  It can be on the same sheet or a different sheet.  You don’t have to specify every heading that is in the original database in step 2; you only need to specify which headings (fields) that you are interested in. In the example above I have not included the Cost column, even though it shows up in my filter criteria.
5. Select the Unique records only checkbox if you just want unique values. In the example above I have not selected this checkbox so every record matching my criteria will display.  In the next example I filter these results and select the Unique records only checkbox.
6. Click OK.

Here is the output from this advanced filter:

Filter Criteria

Here are some examples of filter criteria:

Filter Criteria	Filter Meaning
=	Blanks
<>	Non-blanks
>=41898	All dates >= 16 Sept 2014
>5	Values greater than 5
<>Not applicable	Text not equal to “Not applicable”
S*day	Text that starts with “S” and ends with “day”
Toast	Text equal to “Toast”

To set up your filters, put all the headings that you want to filter on in the same row.  Then put your filter criteria underneath the headings.  Some points to note are:

• Filters or conditions all in the same row must all be satisfied.  This is commonly called an “AND” type condition.
• Filters or conditions in one row are related to other rows by an “OR” type condition. In other words, to be selected by the advanced filter the record in the database or list can satisfy the first row of conditions OR the second row of conditions OR the third row…
• The same heading can appear more than once in the list of filter headings.  For example, if you want to exclude blanks AND you want to exclude “Toast” then you can use two column headings called “Food” and put a different condition underneath each.
• If you specify just the column headings of your filter and not the filtered rows underneath it in the Criteria range: field then no filters are applied to your data, i.e. you select everything. Generally, this would only be useful if you are after a list of unique records or you want to make an exact copy of certain columns and place it somewhere else.  I stumbled upon this by accident but it turns out to be a very useful trick to resolve a potential problem when writing a macro.

See the Further Information section below for more examples of filter criteria.

Unique Records Example

You can filter the original database for unique records or you may like to filter the extracted data (output data) for unique records.  Recently, I applied a second advanced filter to a list of names, counted how many times each occurred and sent each one an email to alert them that they had some actions to do.

Let’s use the extracted data in the example above to get a unique list of the values in the Food column excluding blanks.

The method is essentially the same as in the first example except this time the Unique records only checkbox has been selected.

Here is what the results look like:

Avoid the Pitfall in a Macro

If you are applying the advanced filter more than once then it is possible that your macro may not work after the first time.

The advanced filter “remembers” your values for the list range, criteria range and copy to range by assigning them to the following named variables: “_FilterDatabase” (hidden name), “Criteria” and “Extract”, respectively.

When you write the macro code for the advanced filter, you need to specify the list range. The copy to range is only relevant if you are copying the filtered data to a new location, otherwise it is ignored.

The criteria range is the interesting one.  It is an optional argument. According to the Microsoft Office help, if it is not specified then there are no criteria. In practice, if it is not specified AND the Criteria named range exists (e.g. through a previous application of the Advanced Filter) then it uses this named range as its criteria. (I spent hours trying to work out why my second advanced filter for unique records wasn’t working!)

The easiest solution I found was to just specify the criteria by referencing a filter heading without any filter rows.  This meant that no filter was applied to the data, which was what I wanted – see my comment in the Filter Criteria section above.

Further Information

• Useful information on selection criteria including wildcards
• http://www.contextures.com/xladvfilter01.html
• Useful information on filtering by dates, especially if you don’t use US date format
• http://www.ozgrid.com/VBA/autofilter-vba-dates.htm