Lookup Function?

P

pomalley

My spreadsheet contains more than 3000 records. I want to retrieve only
those records whose status is Closed, Failed, Implemented, or Imp(d); Status
is Column B in the worksheet. Once I identify those records, I want to
automatically copy them to another spreadsheet. I do not want any blank
rows, only the records that meet the criteria. Any suggestions, please
advise. Thank you in advance for your help.
 
B

Bob Phillips

Put this formula in a cell row 2

=IF(A2={"Closed","Failed","Implemented","Imp(d)"},"Copy","")

and copy down for all your records. Then use the Autofilter
(Data>Autofilter), and filter on Copy, then just copy the visible rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
P

pomalley

It's a great idea, but doesn't seem to recognized records with criteria for
failed, implemented or imp(D). Is there some way to combine the query so it
will find all records with the closed, failed, implemented, imp(d) criteria.
The count with this formula is off by about 200 records. I was hopeful that
I could automate that copy process as well. I'm working with what you
provided, but if you have more, wonderful. Thanks.
 
A

Alan Beban

pomalley said:
It's a great idea, but doesn't seem to recognized records with criteria for
failed, implemented or imp(D). Is there some way to combine the query so it
will find all records with the closed, failed, implemented, imp(d) criteria.
The count with this formula is off by about 200 records. I was hopeful that
I could automate that copy process as well. I'm working with what you
provided, but if you have more, wonderful. Thanks.

:
Click to expand...
About how many records do you expect to meet the criteria?

Alan Beban
 
H

Harlan Grove

Bob Phillips wrote...
....
=IF(A2={"Closed","Failed","Implemented","Imp(d)"},"Copy","") ....
Click to expand...
....

Looks like Bob forgot an OR() call. Try

=OR(A2={"Closed","Failed","Implemented","Imp(d)"})

and filter on TRUE values.
 
B

Bob Phillips

Sorry, I gave the wrong formula. I meant to use

=IF(OR(A2="Closed",A2="Failed",A2="Implemented",A2="Imp(d)","Copy","")
 
P

pomalley

Thanks everybody. I surprised myself and figured it out before I got your
comments. This was just the first step in the process to retrieve the data.
I'll be back in touch, but I sure appreciate your help. Thanks again.
 
Top