Excel Filter Macro: Shark Week 2011

image It’s hard to believe that Excel Advanced Filter Week is drawing to a close. I hope that the three filter feeding shark species, and you, have enjoyed this tribute to Discovery Channel’s Shark Week.
Today, we’ll see how to record a macro while using the Advanced Filter, and edit that macro, so it will automatically adjust if the data changes.

Advanced Filter Results are Static

When you run an Advanced Filter, and copy data to a different location, the copied data is not linked to the original data. If the original data changes, the copied data won’t automatically update. You could run the Advanced Filter again, to update the copied data.
If you plan to update the original data frequently, you can save time and effort, by creating a macro to run the Advanced Filter.

Record the Advanced Filter Steps

In this example, we recorded a macro while filtering the top orders onto a different worksheet. The original data is on the Orders sheet, and the copied data is on the TopOrders sheet.
Here is the code that was recorded by the Excel macro recorder. I added a few line breaks to make it narrower.
AdvancedFilterMacro01

Edit the Advanced Filter Macro

After you record the macro, you can edit the code, to make it flexible, and to remove any unnecessary lines of code.
In the recorded code, you can delete the following:

  • comment lines, that start with an apostrophe.
  • lines that end with Select

The list range in the code is set as “A1:D15”. You can change that to the CurrentRegion for cell A1, so the range will automatically adjust, if rows are added or removed.
Sheets(“Orders”).Range(“A1”).CurrentRegion
The other change you should make is to add the sheet name for the CopyToRange. Then, if the destination sheet is not active when you run the macro, it will still work correctly.
CopyToRange:=Sheets(“TopOrders”).Range(“A1:D1”)

The Edited Advanced Filter Macro

Here is the final version of the edited Advanced Filter macro.

Sub TopOrderFilter()
Sheets("Orders").Range("A1").CurrentRegion.AdvancedFilter _
   Action:=xlFilterCopy, _
   CriteriaRange:=Sheets("Orders").Range("F1:F2"), _
   CopyToRange:=Sheets("TopOrders").Range("A1:D1") _
   , Unique:=False
End Sub

Note: In Excel 2007 and later, when you save the file, use the Binary, or Macro-Enabled File Type.

Download the Advanced Filter List Workbook

To see the sample data, and test the filter, you can download the Advanced Filter Macro sample workbook. The file is in Excel 2007 format, and is zipped. The file contains macros, so enable them if you want to test the code.

Watch the Advanced Filter Macro Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.

Or watch on YouTube: Excel Advanced Filter Macro
___________________

Excel Filter for List Items: Shark Week 2011

On Monday, we declared this Excel Advanced Filter Week, in honour of the three filter feeding shark species. Who said Excel wasn’t exciting?

Today, we’ll see how to use the Advanced Filter to select only the items that are in a list, when running the filter.

Advanced Filter Criteria Range

For this Advanced Filter, we want to filter for orders that total more than $1000, and have that criterion in the criteria range, shown below.

We also want to limit the products – only including the items that are listed in column I.

AdvancedFilterList01 

Filter for List Items in Advanced Filter

For a short list of items, you could create multiple rows in the criteria range, and list each item separately. For longer lists, that would be impractical.

Instead, you can use a formula, to check each row, and see if its product is in the list. We’ll add a second column to the criteria range, and put the formula there.

In this example, the first product data is in cell C2, so the formula in cell G2 is:

=COUNTIF(I:I,C2)

The COUNTIF function returns the number of instances of the product in the list in column I. If the product isn’t found, the count is zero. Only the records with a count greater than zero would pass through the filter.

AdvancedFilterList02 

Because you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings. You can either clear the heading cell in the criteria range, or type a different heading. I left cell G1 blank, and the criteria range is F1:G2.

Run the Advanced Filter

After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.

In this example, the filter is started from the ListOrders sheet, and the list and criteria range are on the Orders sheet.

AdvancedFilterList03 

Download the Advanced Filter List Workbook

To see the sample data, and test the filter, you can download the Advanced Filter for List Items sample workbook. The file is in Excel 2007 format, and is zipped.

Watch the Advanced Filter for List Items Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.

Or watch on YouTube: Excel Advanced Filter Items in a List

___________________

Excel Filter for Blanks: Shark Week 2011

whale shark filter feeder Last year, we celebrated the Discovery Channel’s Shark Week, by using the LARGE and FLOOR functions.
This year, we’ll pay tribute to the three known species of sharks that are filter feeders, by declaring this Excel Advanced Filter Week.
Yes, we’ll have three fun-filled, action-packed days of Excel filtering fabulousness – one day for each filter feeding shark. Please hold your applause until all three articles have been posted.

Advanced Filter Criteria Range

We’ll kick off the week’s celebrations by filtering rows with missing data (blank cells) to a different worksheet. When you’re using an Advanced Filter, usually you would enter a heading, and one or more criteria, in a criteria range, like the one shown below.
In this example, you would be filtering the customer order list for any orders with Cookies as the product.
AdvancedFilterBlank01
However, if you want to filter orders with a blank cell for Product, you can’t just leave the criteria range blank. A blank criteria cell is interpreted as “No criteria”, so all the records would pass through the filter. That might be fine for a shark, but not for an Excel report.
AdvancedFilterBlank02

Filter for Blanks in Advanced Filter

Instead of leaving the criteria cell blank, you can use a formula, to check for empty cells. In this example, the first product data is in cell C2, so the formula is:
=C2=””
The two double quote marks represent an empty string, so if C2 is not blank, the formula result is FALSE. Only the records that calculate to TRUE would pass through the filter.
AdvancedFilterBlank03

Remove the Criteria Range Heading

If you’re using a formula in an Advanced Filter criteria range, the heading can’t match any of the source data headings. You can either clear the heading cell in the criteria range, or type a different heading.
I usually clear the heading cell, because that’s quick and easy!
AdvancedFilterBlank04

Run the Advanced Filter

After you set up the criteria range, you can run the Advanced Filter. Remember, if you want the results on a different worksheet, select that destination sheet before you run the filter.
In this example, the filter is started from the Blank Orders sheet, and the list and criteria range are on the Orders sheet.
AdvancedFilterBlank05

Download the Advanced Filter Blanks Workbook

To see the sample data, and test the filter, you can download the Advanced Filter for Blanks sample workbook. The file is in Excel 2007 format, and is zipped.

Watch the Advanced Filter for Blanks Video

To see the steps for setting up the criteria range, and running the filter, you can watch this short Excel Video tutorial.

Or watch on YouTube: Excel Advanced Filter Finds Blank Cells
___________________

AutoFilter For Multiple Selections

With data validation and some programming, you can select multiple items from a drop down list, and show the selections in a single cell.
MultiSelectFilter01
That technique is helpful in some situations, but it can make filtering difficult. With an AutoFilter turned on, only the combined results show in the filter options, not the individual items.
MultiSelectFilter02

Filter for a Single Item

This week, Ben emailed to ask how to filter that combined list for a single item. For example, how could you see all the rows where Two was selected?
If you’re using Excel 2010, you can type in the filter Search box, just above the list of filter options. As you type, the options are automatically filtered to show only the items that contain the text that you are typing.
In the screen shot below, I have typed “two”, and only the matching options remain in the list. Click OK, and only the selected items are visible in the filtered list.
MultiSelectFilter03

Filter in Excel 2007

For Excel 2007 AutoFilters, where there is no Search box, you can use the Text Filters command. Click the Contains command, to open the Custom AutoFilter box.
MultiSelectFilter04
Then, type the option or options that you want to filter.
MultiSelectFilter05

Filter in Excel 2003 and Earlier

For earlier versions of Excel, use the Custom option, at the top of the AutoFilter drop down list, to open the Custom AutoFilter dialog box.
MultiSelectFilter06

Watch the Video

To see the AutoFilter Search box in action, please watch this short Excel tutorial video.

__________________

Excel Function Friday: Subtotal and Sumproduct with Filter

Last week, we used the Excel SUBTOTAL function to sum items in a filtered list, while ignoring the hidden rows.
subtotal05b
In the comments for that blog post, Sam mentioned that you can also use SUBTOTAL with functions like SUMPRODUCT, to do additional sums or counts, based on the visible data in a filtered table. Sam sent me his sample Excel file, to show how he uses this technique.

Create Named Ranges

Sam’s workbook has a list with Product, Region and Amount fields. He created dynamic named ranges for the entries in each field, using INDEX and COUNTA.
sumproductfiltersam01
Note: If you’re using Excel 2010 or Excel 2007, and a named table, you can use table references to create the names.
sumproductfiltersam01b

The SUMPRODUCT Function

With the a simple SUMPRODUCT function, you could sum the amounts for all the North region rows. This works well if the list is not filtered.
  =SUMPRODUCT((Region=A2)*(Amt))
sumproductfiltersam02
However, if the list is filtered, the total for North region still calculates as 545, even though only one amount, 55, is visible.
sumproductfiltersam02a

Use SUBTOTAL with SUMPRODUCT

To solve the problem, Sam created another named range, vAmt, that is based on the Amt range.
  =SUBTOTAL(109,OFFSET(Amt,ROW(Amt)-ROW(‘1’!$C$6),,1,1))
The named range uses the SUBTOTAL function to return an array of the values, with zero showing if the row is hidden.
When the vAmt range is used in a SUMPRODUCT formula, it shows the total for only the visible rows. In the screen shot below, the South region is selected, and the two visible amounts for that region are totaled correctly in cell C2
sumproductfiltersam03
If you select the vAmt in the formula, and press the F9 key, you can see the effect of the SUBTOTAL function. All the amounts show as zero, except for the three visible amounts — 22, 36 and 19.
sumproductfiltersam04

Download the Sample File

To see Sam’s workbook and formulas, you can download the SUMPRODUCT SUBTOTAL sample file. The file is zipped, and it is in Excel 2003 file format.
___________________

Excel AutoFilter or Advanced Filter?

filterclog Do you ever use the Excel Advanced Filter feature? Or is all your filtering done with an AutoFilter?

AutoFilter Benefits

For ease of use, it’s hard to beat the Excel AutoFilter feature. Just click the Filter command on the Excel Ribbon’s Data tab, and the filter is ready to go.
FilterAdvAuto01
You can click the drop down arrows in the column headings, and use the check boxes to select the items you want in the filter. Or, use the Text, Date or Number Filters commands, for variations on the filters.
filteradvauto02
Another quick way to filter is to right-click on a cell in the list, then click Filter, and click Filter by Selected Cell’s Value.
filteradvauto03

Advanced Filter Benefits

The Excel Advanced Filter isn’t as easy to use (I guess that’s why they call it Advanced 😉 ), but it does have some benefits that make it worth the effort.
After you click the Advanced command on the Excel Ribbon’s Data tab, the Advanced Filter dialog box opens. Fill in the details, and then click the OK button to filter the data.
advancedfilter04

Filter Data to Anther Sheet

The main reason that I use an Advanced Filter, especially when programming in Excel, is that it’s a quick and efficient way to copy specific data to a different worksheet.
In the screen shot above, the option is selected to copy the filtered data to another location. You can use that option to quickly create a report for each department, or salesperson, and email the results.
NOTE: You have to start the Advanced Filter from the destination sheet. Click this link for detailed instructions and a video.

Create a List of Unique Items

Excel 2007 introduced the Remove Duplicates feature, but I still use an Advanced Filter to create lists of unique items. The Remove Duplicates feature strips the duplicates out of the list that is selected, so you have to remember to work on a copy of the list, if you want to keep the original list intact.
With an Advanced Filter, no items are removed from the list, so you don’t risk losing any of your original data.

Complex Filters

With an Advanced Filter, you can create a criteria range on the worksheet, and see at a glance what the filter settings are. You can also create complex filters with AND/OR settings that go beyond what an AutoFilter can do.
For example, with an AutoFilter, you can select two specific customers, and two products for those selected customers.
filteradvauto04
With an Advanced Filter, you can create OR conditions between columns, such as Customer A OR Product B — you can’t do that in an AutoFilter!
filteradvauto05
_________
Save

Turn Off Filters in Excel Table

Now that the 30 Excel Functions in 30 Days challenge has ended, it’s time to look at a few other features. On the Contextures page on Facebook, Lee suggested AutoFilters as a topic for some February posts. Thanks Lee!

Filters in Excel Tables

When you create an Excel Table in Excel 2007 or Excel 2010, autofilter arrows are automatically added to the heading cells. Most of the time, this is a welcome feature, and the arrows make it easy to sort and filter the columns.
ExcelTableFilters01
You can even create more than one Excel table on a worksheet, and have a separate filter on each table. I use this feature occasionally, when creating small tables on a summary sheet. Just remember to stack the tables, instead of placing them side by side. That way, the filtered rows in one table won’t affect the others.
Note: If you don’t use the Excel Table feature, you’re limited to a single AutoFilter (or Advanced Filter) per worksheet.
ExcelTableFilters04

Turn Off the Automatic Filters

Sometimes, though, you don’t want to have those filter arrows available. For example, you might set up tables because they’re a quick and easy way to create a dynamic range. Or, maybe you like the pretty colours and automatic total row.
But, you don’t want to encourage users to filter the data — all the rows should always be visible. And you know those pesky users will click the arrows, if they’re temptingly available.

To turn off the filters:

  1. Select a cell in the Excel Table
  2. On the Ribbon’s Data tab, click the Filter command, to turn off the filter.

ExcelTableFilters02

Keep the Excel Table Features

After you turn off the filters, the Excel Table is still recognized as a table, so you can continue to use all its other features. Even though the filter arrows are gone, you can add a Table Style, and show Banded Rows.
ExcelTableFilters03
Or, create formulas that use structured referencing, instead of cell references.
ExcelTableFilters05
__________

Excel Advanced Filter Update

Here’s the final video update for this week — Automatically Copy Excel Data to Another Sheet. This video now shows the steps in Excel 2010 or Excel 2007, instead of Excel 2003.
To wrap up this week of updates, here are links to a few of my previous Excel Advanced Filter articles and posts. You probably know all the basics, but maybe you’ve missed a few of these tips and tricks.

Note: Remember — the 30 Excel Functions in 30 Days challenge starts January 2nd. See you then!

Watch the Excel 2010 Advance Filter Video

See how to automatically copy data to a different worksheet, with an Advanced Filter, in Excel 2010 or Excel 2007.

Video Link: Automatically Copy Excel Data to Other Sheet http://www.youtube.com/watch?v=BdG43GjIwRg
_________________

Excel AutoFilter With Criteria in a Range

iconfilter2In Excel 2003, and earlier versions, an AutoFilter allows only two criteria for each column. If you want to filter for multiple criteria, you can use an Advanced Filter. List all the criteria on a worksheet, and use that list (and its heading cell) as the criteria range.
In Excel 2007 and Excel 2010, the AutoFilter feature has been improved, and you can select multiple criteria in each column.
autofiltermulti

Record an AutoFilter Macro

If you record a macro while selecting criteria in Excel 2007, it will look something like this:
autofiltermulti02
The criteria are entered as an array, showing all three items that were selected in the drop down list.

Create Your Own Array

In the Contextures mail bag this week, someone asked if it’s possible to create this type of AutoFilter criteria array from a list on the worksheet. And the answer is yes, you can!
For this example, there’s a dynamic named range — CritList — on the Lists worksheet. The items in the CritList range will be used as the AutoFilter criteria array.
autofiltermulti03
On the Orders sheet, the fourth column — Products — will be filtered using this criteria list.
autofiltermulti04

AutoFilter Code With Array

In an Excel VBA procedure, you can create a variable to store the values from the CritList named range. Define this variable as a Variant, and it will store the values as an array.

vCrit = rngCrit.Value

Then, to use this variable as the AutoFilter criteria list, transpose the array, so it’s read as a row, instead of a column. If you don’t transpose the array, only the first item would be used in the criteria array. (Or, create your worksheet list in a row, instead of a column, and you won’t have to transpose it.)

Criteria1:=Application.Transpose(vCrit)

Here’s the complete code for the AutoFilter:

Sub FilterRangeCriteria()
Dim vCrit As Variant
Dim wsO As Worksheet
Dim wsL As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Set wsO = Worksheets("Orders")
Set wsL = Worksheets("Lists")
Set rngOrders = wsO.Range("$A$1").CurrentRegion
Set rngCrit = wsL.Range("CritList")
vCrit = rngCrit.Value
rngOrders.AutoFilter _
    Field:=4, _
    Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues
End Sub

Download the AutoFilter Array Sample File

To see the sample code and the named range, you can download the AutoFilter Criteria Array sample file. It’s in Excel 2007 format, zipped, and you can enable macros when you open the file.
_________

Excel 2007 AutoFilter Dynamic Dates

icondynamic Over on the Contextures website, I’ve updated the AutoFilter Intro page, so it now covers the basics for Excel 2007 AutoFilters. However, many people are still using an older version of Excel, so I’ve moved the original material to the Excel 2003 AutoFilter Basics page.
AutoFilters are easier to use in Excel 2007 and Excel 2010, and the filter and sort options are automatically added in the top row, if you format your list as an Excel Table.

Filter for Dynamic Date Ranges

Among the new AutoFilter features that were introduced in Excel 2007 are dynamic date ranges. A Dynamic Date Range is one that changes automatically, as time moves forward. For example, you could select Yesterday, which will represent a different date, every day that you open the Excel file.
Excel2007AutoFilterDates
Unfortunately, the dynamic dates are only semi-dynamic, and they don’t magically change when you open the workbook at a later date. You’ll need to update the filter to see the current information.
You can update the Excel 2007 AutoFilter manually, by clicking Reapply on the Excel Ribbon. Or, you could add a bit of code to the Workbook_Open event, to reapply the filters automatically.
autofilter2007_16

Learn More About Excel 2007 AutoFilters

If you’re not familiar with the new features in Excel 2007 and Excel 2010 AutoFilters, you can learn more at Excel 2007 AutoFilter Basics.
_____________