data extract

[Guest]

I have a worksheet example: column A is "date", column B is "work", column C
is "amount".

05/01/2006 test 24
05/01/2006 12
05/01/2006 maybe 24

05/02/2006 18
05/02/2006 test 20

I would like a formula to extract the entire row of data when "work" is
equal to test. I tried using If and Offset functions but I get blank rows of
data (because of matching). My desired output would be:

05/01/2006 test 24
05/01/2006 test 20

Thank you.

 

[Peo Sjoblom]

You could use either filter, for autofilter you would filter work column on
test, then select and copy somewhere else, for advanced filter assuming you
have a header called Work

in let's say H1 put Work and in H2 put test

then apply filter>advanced filter, select the whole table, then as criteria
range use $H$1:$H$2

then copy to another location


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

thank you for reply.

I don't think this will work quite the way I want. I would still like to
use formulas as the "work" selection will change depending on user selection.
In the example I used test but it could be maybe. I want to automatically
insert data into cells with out having to copy/paste and using filter as you
suggested.

 

[Peo Sjoblom]

Are you saying that by using a formula you wouldn't have to change criteria?
Anyway with test in F1, headers in A1:C1 and the data in A2:C6

=IF(ROWS(B$2:B2)<=COUNTIF($B$2:$B$6,$F$1),INDEX(A$2:A$6,SMALL(IF($B$2:$B$6=$F$1,ROW($B$2:$B$6)-ROW($B$2)+1),ROWS(B$2:B2))),"")


entered with ctrl + shift & enter

copy across 2 columns then down as long as needed, you need to format the
date cells once you are done or else you will just see the date serial
numbers


--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Biff]

Hi!

My desired output would be:
05/01/2006 test 24
05/01/2006 test 20

Shouldn't that be:

05/01/2006 test 24
05/01/2006 test 20

Assume your data is in the range A1:C5.

Enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER:

=IF(ROWS($1:1)<=COUNTIF($B$1:$B$5,"test"),INDEX(A$1:A$5,SMALL(IF($B$1:$B$5="test",ROW(A$1:A$5)-ROW(A$1)+1),ROWS($1:1))),"")

Copy across to 3 cells then down until you het blanks.

Format the date cell as DATE.

Biff

 

[Peo Sjoblom]

LOL!

Shouldn't that be?

05/01/2006 test 24
05/02/2006 test 20


Peo

 

[Biff]

Ooops!

Biff

LOL!

Shouldn't that be?

05/01/2006 test 24
05/02/2006 test 20


Peo

 

[Guest]

Perfect!!! I got it to work with a little modification.

Thanks to both of you !!!