data extract

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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
 
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.
 
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
 
Hi!
My desired output would be:
05/01/2006 test 24
05/01/2006 test 20
Click to expand...

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
 
Back
Top