On Tue, 9 Aug 2005 11:03:16 -0500, "Leslie Coover" wrote:

Not sure what you mean?

A1:A6 = {a, b, c, d, e, f}
B1:B3 = {a, d, f}

how should I reorganize the data to use the formula
=COUNTIF($A$9:$A$11,B9)=0
as a criteria in an advance filter and what should I enter in
List Range box?

Thanks.

OK , you've reversed your lists. And you do have to have things laid out in a
defined way to get the advanced filter to work. Here's one way.

I assume that the result you want is {b,c,e}.

Set up a worksheet as follows

A B
List1 List2
a a
b d
c f
d
e
f

(In columns A & B; and Rows 1-7; note that a header row is used)

E1: <empty
E2: =COUNTIF($B$2:$B$4,A2)=0

Data/Filter/Advanced Filter
Action: Copy to another location
List Range: $A$1:$B$7
Criteria Range: $E$1:$E$2
Copy to: G1
<OK

In columns G & H you will see:

List1 List2
b d
c f
e

You are only interested in List1 so you can either delete H1:H3 or copy column
G to wherever. Or set things up to display the results some other way.

The above could also be done using a macro. Probably you should use the macro
recorder if you choose this option.



--ron