data extract
 

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.

data extract
 

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 filteradvanced 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



"Mona" wrote in message
...
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.

data extract
 

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" wrote:

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 filteradvanced 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



"Mona" wrote in message
...
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.

data extract
 

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


"Mona" wrote in message
...
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" wrote:

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 filteradvanced 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



"Mona" wrote in message
...
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.

data extract
 

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

"Mona" wrote in message
...
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.

data extract
 

LOL!

Shouldn't that be?

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


Peo

"Biff" wrote in message
...
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

"Mona" wrote in message
...
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.

data extract
 

Ooops!

Biff

"Peo Sjoblom" wrote in message
...
LOL!

Shouldn't that be?

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


Peo

"Biff" wrote in message
...
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

"Mona" wrote in message
...
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.

data extract
 

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

Thanks to both of you !!!

"Biff" wrote:

Ooops!

Biff

"Peo Sjoblom" wrote in message
...
LOL!

Shouldn't that be?

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


Peo

"Biff" wrote in message
...
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

"Mona" wrote in message
...
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.