Tuesday, September 06, 2005

Using the Advanced Filter in Excel


There are some specific instructions to be followed in order to use the Advanced Filter to filter a list and return the result to another location.
From the Help file:
The Advanced Filter command on the Data menu lets you use complex criteria (criteria: Conditions you specify to limit which records are included in the result set of a query. For example, the following criterion selects records for which the value for the Order Amount field is greater than 30,000: Order Amount > 30000.) to filter a range, but it works differently from the AutoFilter command in several important ways.

It displays the Advanced Filter dialog box instead of the Custom AutoFilter dialog box.
You do not type the complex criteria in the Advanced Filter dialog box as you do in the Custom AutoFilter dialog box. Rather, you type the complex criteria in a criteria range on the worksheet and above the range you want to filter. Excel uses the separate criteria range in the Advanced Filter dialog box as the source for the complex criteria.
Although you can filter a range in place, like the AutoFilter command, the Advanced Filter command does not display drop-down lists for the columns.
Insert at least three blank rows above the range that can be used as a criteria range. The criteria range must have column labels. Make sure there is at least one blank row between the criteria values and the range.
Here is an example. You have a list of values in a spreadsheet with a header called salaries. This is in Column A.

First make sure there is plenty of room - at least 3 blank rows between row 1 and the header of the list.
Then copy the list header to cell A1
In cell A2 type ="<50000" - including the quotes. This will create a criteria that will return all salaries under $50,000.
This creates a text criteria. Of course you could use the concatenate formula to derive this.

Select the range of data in the list including the header but not the criteria range.
Now click Data | Filter | Advanced Filter on the menu bar.
Click the radio button for Copy to another location.
The list range should already be correct. (selected data)
The criteria range needs to include the header so in this instance $A$1:$A$2
Click in the Copy To box and then click the place on the spreadsheet that you want the result returned and click OK. If you only want one instance of each duplicate value check the Unique records only box first.


Posted by: Jethro | 1:29 PM |