capturing all of the data

[jimbo]

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

 

[T. Valko]

Use autofilter and filter on positive. You can copy the results and paste
into a new sheet if you want.

 

[Gord Dibben]

Biff

I read it as OP wants all records for a patient including negative records
if a patient has at least one positive record.

Probably have to filter a couple times.

Too late for thinking right now.............night all


Gord

 

[NoodNutt]

G'day Jimbo

Expanding on Biff's response, you can automate the process by using the code
at the link below.

http://www.rondebruin.nl/copy2.htm

It will filter, then copy your filtered ranged based on your criteria and
paste to a new worksheet for you.

attach the code to a Macro Button on your WS.

HTH
Mark.

 

[jimbo]

Use autofilter and filter on positive. You can copy the results and paste
into a new sheet if you want.

--
Biff
Microsoft Excel MVP






- 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

 

[jimbo]

Biff

I read it as OP wants all records for a patient including negative records
if a patient has at least one positive record.

Probably have to filter a couple times.

Too late for thinking right now.............night all

Gord



- Show quoted text -

you are correct, only those with both positive and negative results

 

[M Kan]

I think a helper column would help you here. If I understand, you want to
exclude anyone who only has negative results, e.g., count of positive
results=0.

If this is the case, add a column and use the following formula: I'll
assume your first john smith appears in cell A2 and extends to row 10000.

=sumproduct(--($A$2:$A$10000=A2),--($C$2:$C$10000="positive"))=0

This would give you a column of TRUEs and FALSEs. You simple filter out the
TRUEs

 

[T. Valko]

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

Use autofilter and filter on positive. You can copy the results and paste
into a new sheet if you want.

--
Biff
Microsoft Excel MVP






- 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