Try this...

Add a helper column with this formula:

=IF(SUMPRODUCT(--(A$2:A$2000=A2),--(C$2:C$2000="positive")),"x","")

Copy down to the end of data. Then filter on this helper column on "x".

--
Biff
Microsoft Excel MVP


"jimbo" wrote in message
...
On Jul 26, 7:22 pm, "T. Valko" wrote:
Use autofilter and filter on positive. You can copy the results and paste
into a new sheet if you want.

--
Biff
Microsoft Excel MVP

"jimbo" wrote in message

...



I have a list of people with test resullts. I need to identify the
infividuals who are positive, and list all of their results, e.g. in
the following example, I would want all of the results of john smith
and mary kay because they have one or more positive results, but not
any of the results of jim jones or susan rae, because all of their
results are negative. This is easy enough when there is a limited
number of individuals, but when it is in the 10s of thousands, it's
impossible for me to capture anything more thatn the positive from a
single date. But I need all of the results of any individual with one
or more positives. Any suggestions?
john smith 3/4/2008 negative
john smith 4/4/2008 positive
john smith 5/4/2008 negative
john smith 6/4/2008 negative
john smith 7/4/2008 negative
jim jones 3/4/2008 negative
jim jones 4/4/2008 negative
jim jones 5/4/2008 negative
jim jones 6/4/2008 negative
jim jones 7/4/2008 negative
mary kay 3/4/2008 negative
mary kay 4/4/2008 negative
mary kay 5/4/2008 negative
mary kay 6/4/2008 positive
mary kay 7/4/2008 negative
mary kay 8/4/2008 negative
susan rae 5/4/2008 negative
susan rae 6/4/2008 negative
susan rae 7/4/2008 negative
susan rae 8/4/2008 negative- Hide quoted text -

- Show quoted text -

That will get me all individuals with positive results, but will not
include negative results. I need something along the lindes of any
indivicual with both positive and negative results, and exclude all
individuals with all negative results