Fiiltering help. please?

[Ed]

I'm trying to filter a worksheet to one value in one column, then filter
another column to unique values only. The Unique Values doesn't seem to be
available to the AutoFilter, and applying the Advanced Filter after
AutoFiltering the other column seems to undo the AutoFilter. I looked at
both Auto and Advanced Filters in Help, and looked through Contextures and
Excel 2000 Bible, but I can't seem to see (or perhaps grasp) what I'm
missing. Any help is appreciated.

Ed

 

[Debra Dalgleish]

Use an Advanced Filter to filter both columns.

In the criteria area, enter the column heading and criterion
for the column that has one value to filter.
In the adjacent column, leave the heading cell blank.
In the cell below, enter a formula to find unique values, e.g. for
column H, with data starting in row 2:

=COUNTIF($H$2:$H$500,H6)=1

Your criteria range would look similar to this:
| Region | |
| Ontario | =COUNTIF($H$2:$H$500,H6)=1 |

Then, apply the Advanced Filter.

 

[Peo Sjoblom]

You can do that in one fell swoop using the both filters, first autofilter,
assume you use
A and B starting in A4 with a header, you want to filter A for value "a" and
B for unique values.. Insert a help column in C, in C5 use

=A5&B5

copy down

Now in D5 use

=COUNTIF($C$5:C5,C5)=1

copy down. Apply autofilter and filter on A and then filter on D using TRUE

Using the advanced filter, create the same help column, for criteria range
select H1:H2,
in H2 put this formula

=AND(A5="a",COUNTIF($C$5:C5,C5)=1)

then select the table as list range, and copy to another location