Hi,
Filter by using advanced criteria
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.
1. 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.
2. In the rows below the column labels, type the criteria you want to match.
3. Click a cell in the range.
4. On the Data menu, point to Filter, and then click Advanced Filter.
5. To filter the range by hiding rows that don't match your criteria, click
Filter the list, in-place.
To filter the range by copying rows that match your criteria to another area
of the worksheet, click Copy to another location, click in the Copy to box,
and then click the upper-left corner of the area where you want to paste the
rows.
6. In the Criteria range box, enter the reference for the criteria range,
including the criteria labels.
To move the Advanced Filter dialog box out of the way temporarily while you
select the criteria range, click Collapse Dialog .
7. To change how the data is filtered, change the values in the criteria
range and filter the data again.
Notes
You can name a range Criteria, and the reference for the range will appear
automatically in the Criteria range box. You can also define the name
Database for the range of data to be filtered and define the name Extract for
the area where you want to paste the rows, and these ranges will appear
automatically in the List range and Copy to boxes, respectively.
When you copy filtered rows to another location, you can specify which
columns to include in the copy. Before filtering, copy the column labels for
the columns you want to the first row of the area where you plan to paste the
filtered rows. When you filter, enter a reference to the copied column labels
in the Copy to box. The copied rows will then include only the columns for
which you copied the labels.
Challa Prabhu
