software

I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;, column C
is quot;amountquot;.

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 quot;workquot; 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 filtergt;advanced filter, select the whole table, then as criteria
range use $H$1:$H$2

then copy to another location--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;Monaquot; gt; wrote in message
...
gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;, column
gt;C
gt; is quot;amountquot;.
gt;
gt; 05/01/2006 test 24
gt; 05/01/2006 12
gt; 05/01/2006 maybe 24
gt;
gt; 05/02/2006 18
gt; 05/02/2006 test 20
gt;
gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt; equal to test. I tried using If and Offset functions but I get blank rows
gt; of
gt; data (because of matching). My desired output would be:
gt;
gt; 05/01/2006 test 24
gt; 05/01/2006 test 20
gt;
gt; Thank you.
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 quot;workquot; 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.

quot;Peo Sjoblomquot; wrote:

gt; You could use either filter, for autofilter you would filter work column on
gt; test, then select and copy somewhere else, for advanced filter assuming you
gt; have a header called Work
gt;
gt; in let's say H1 put Work and in H2 put test
gt;
gt; then apply filtergt;advanced filter, select the whole table, then as criteria
gt; range use $H$1:$H$2
gt;
gt; then copy to another location
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;Monaquot; gt; wrote in message
gt; ...
gt; gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;, column
gt; gt;C
gt; gt; is quot;amountquot;.
gt; gt;
gt; gt; 05/01/2006 test 24
gt; gt; 05/01/2006 12
gt; gt; 05/01/2006 maybe 24
gt; gt;
gt; gt; 05/02/2006 18
gt; gt; 05/02/2006 test 20
gt; gt;
gt; gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt; gt; equal to test. I tried using If and Offset functions but I get blank rows
gt; gt; of
gt; gt; data (because of matching). My desired output would be:
gt; gt;
gt; gt; 05/01/2006 test 24
gt; gt; 05/01/2006 test 20
gt; gt;
gt; gt; Thank you.
gt;
gt;
gt;

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)lt;=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))),quot;quot;)entered with ctrl shift amp; 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
quot;It is a good thing to follow the first law of holes;
if you are in one stop digging.quot; Lord Healeyquot;Monaquot; gt; wrote in message
...
gt; thank you for reply.
gt;
gt; I don't think this will work quite the way I want. I would still like to
gt; use formulas as the quot;workquot; selection will change depending on user
gt; selection.
gt; In the example I used test but it could be maybe. I want to automatically
gt; insert data into cells with out having to copy/paste and using filter as
gt; you
gt; suggested.
gt;
gt; quot;Peo Sjoblomquot; wrote:
gt;
gt;gt; You could use either filter, for autofilter you would filter work column
gt;gt; on
gt;gt; test, then select and copy somewhere else, for advanced filter assuming
gt;gt; you
gt;gt; have a header called Work
gt;gt;
gt;gt; in let's say H1 put Work and in H2 put test
gt;gt;
gt;gt; then apply filtergt;advanced filter, select the whole table, then as
gt;gt; criteria
gt;gt; range use $H$1:$H$2
gt;gt;
gt;gt; then copy to another location
gt;gt;
gt;gt;
gt;gt; --
gt;gt;
gt;gt; Regards,
gt;gt;
gt;gt; Peo Sjoblom
gt;gt;
gt;gt; nwexcelsolutions.com
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Monaquot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;,
gt;gt; gt;column
gt;gt; gt;C
gt;gt; gt; is quot;amountquot;.
gt;gt; gt;
gt;gt; gt; 05/01/2006 test 24
gt;gt; gt; 05/01/2006 12
gt;gt; gt; 05/01/2006 maybe 24
gt;gt; gt;
gt;gt; gt; 05/02/2006 18
gt;gt; gt; 05/02/2006 test 20
gt;gt; gt;
gt;gt; gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt;gt; gt; equal to test. I tried using If and Offset functions but I get blank
gt;gt; gt; rows
gt;gt; gt; of
gt;gt; gt; data (because of matching). My desired output would be:
gt;gt; gt;
gt;gt; gt; 05/01/2006 test 24
gt;gt; gt; 05/01/2006 test 20
gt;gt; gt;
gt;gt; gt; Thank you.
gt;gt;
gt;gt;
gt;gt;
Hi!

gt;My desired output would be:
gt;05/01/2006 test 24
gt;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)lt;=COUNTIF($B$1:$B$5,quot;testquot;),INDEX(A$ 1:A$5,SMALL(IF($B$1:$B$5=quot;testquot;,ROW(A$1:A$5)-ROW(A$1) 1),ROWS($1:1))),quot;quot;)

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

Format the date cell as DATE.

Biff

quot;Monaquot; gt; wrote in message
...
gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;, column
gt;C
gt; is quot;amountquot;.
gt;
gt; 05/01/2006 test 24
gt; 05/01/2006 12
gt; 05/01/2006 maybe 24
gt;
gt; 05/02/2006 18
gt; 05/02/2006 test 20
gt;
gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt; equal to test. I tried using If and Offset functions but I get blank rows
gt; of
gt; data (because of matching). My desired output would be:
gt;
gt; 05/01/2006 test 24
gt; 05/01/2006 test 20
gt;
gt; Thank you.
LOL!

Shouldn't that be?

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

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt;gt;My desired output would be:
gt;gt;05/01/2006 test 24
gt;gt;05/01/2006 test 20
gt;
gt; Shouldn't that be:
gt;
gt; 05/01/2006 test 24
gt; 05/01/2006 test 20
gt;
gt; Assume your data is in the range A1:C5.
gt;
gt; Enter this formula as an array using the key combination of
gt; CTRL,SHIFT,ENTER:
gt;
gt; =IF(ROWS($1:1)lt;=COUNTIF($B$1:$B$5,quot;testquot;),INDEX(A$ 1:A$5,SMALL(IF($B$1:$B$5=quot;testquot;,ROW(A$1:A$5)-ROW(A$1) 1),ROWS($1:1))),quot;quot;)
gt;
gt; Copy across to 3 cells then down until you het blanks.
gt;
gt; Format the date cell as DATE.
gt;
gt; Biff
gt;
gt; quot;Monaquot; gt; wrote in message
gt; ...
gt;gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;, column
gt;gt;C
gt;gt; is quot;amountquot;.
gt;gt;
gt;gt; 05/01/2006 test 24
gt;gt; 05/01/2006 12
gt;gt; 05/01/2006 maybe 24
gt;gt;
gt;gt; 05/02/2006 18
gt;gt; 05/02/2006 test 20
gt;gt;
gt;gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt;gt; equal to test. I tried using If and Offset functions but I get blank
gt;gt; rows of
gt;gt; data (because of matching). My desired output would be:
gt;gt;
gt;gt; 05/01/2006 test 24
gt;gt; 05/01/2006 test 20
gt;gt;
gt;gt; Thank you.
gt;
gt;
Ooops!

Biff

quot;Peo Sjoblomquot; gt; wrote in message
...
gt; LOL!
gt;
gt; Shouldn't that be?
gt;
gt; 05/01/2006 test 24
gt; 05/02/2006 test 20
gt;
gt;
gt; Peo
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt;gt; Hi!
gt;gt;
gt;gt;gt;My desired output would be:
gt;gt;gt;05/01/2006 test 24
gt;gt;gt;05/01/2006 test 20
gt;gt;
gt;gt; Shouldn't that be:
gt;gt;
gt;gt; 05/01/2006 test 24
gt;gt; 05/01/2006 test 20
gt;gt;
gt;gt; Assume your data is in the range A1:C5.
gt;gt;
gt;gt; Enter this formula as an array using the key combination of
gt;gt; CTRL,SHIFT,ENTER:
gt;gt;
gt;gt; =IF(ROWS($1:1)lt;=COUNTIF($B$1:$B$5,quot;testquot;),INDEX(A$ 1:A$5,SMALL(IF($B$1:$B$5=quot;testquot;,ROW(A$1:A$5)-ROW(A$1) 1),ROWS($1:1))),quot;quot;)
gt;gt;
gt;gt; Copy across to 3 cells then down until you het blanks.
gt;gt;
gt;gt; Format the date cell as DATE.
gt;gt;
gt;gt; Biff
gt;gt;
gt;gt; quot;Monaquot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;,
gt;gt;gt;column C
gt;gt;gt; is quot;amountquot;.
gt;gt;gt;
gt;gt;gt; 05/01/2006 test 24
gt;gt;gt; 05/01/2006 12
gt;gt;gt; 05/01/2006 maybe 24
gt;gt;gt;
gt;gt;gt; 05/02/2006 18
gt;gt;gt; 05/02/2006 test 20
gt;gt;gt;
gt;gt;gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt;gt;gt; equal to test. I tried using If and Offset functions but I get blank
gt;gt;gt; rows of
gt;gt;gt; data (because of matching). My desired output would be:
gt;gt;gt;
gt;gt;gt; 05/01/2006 test 24
gt;gt;gt; 05/01/2006 test 20
gt;gt;gt;
gt;gt;gt; Thank you.
gt;gt;
gt;gt;
gt;
gt;
Perfect!!! I got it to work with a little modification.

Thanks to both of you !!!

quot;Biffquot; wrote:

gt; Ooops!
gt;
gt; Biff
gt;
gt; quot;Peo Sjoblomquot; gt; wrote in message
gt; ...
gt; gt; LOL!
gt; gt;
gt; gt; Shouldn't that be?
gt; gt;
gt; gt; 05/01/2006 test 24
gt; gt; 05/02/2006 test 20
gt; gt;
gt; gt;
gt; gt; Peo
gt; gt;
gt; gt; quot;Biffquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Hi!
gt; gt;gt;
gt; gt;gt;gt;My desired output would be:
gt; gt;gt;gt;05/01/2006 test 24
gt; gt;gt;gt;05/01/2006 test 20
gt; gt;gt;
gt; gt;gt; Shouldn't that be:
gt; gt;gt;
gt; gt;gt; 05/01/2006 test 24
gt; gt;gt; 05/01/2006 test 20
gt; gt;gt;
gt; gt;gt; Assume your data is in the range A1:C5.
gt; gt;gt;
gt; gt;gt; Enter this formula as an array using the key combination of
gt; gt;gt; CTRL,SHIFT,ENTER:
gt; gt;gt;
gt; gt;gt; =IF(ROWS($1:1)lt;=COUNTIF($B$1:$B$5,quot;testquot;),INDEX(A$ 1:A$5,SMALL(IF($B$1:$B$5=quot;testquot;,ROW(A$1:A$5)-ROW(A$1) 1),ROWS($1:1))),quot;quot;)
gt; gt;gt;
gt; gt;gt; Copy across to 3 cells then down until you het blanks.
gt; gt;gt;
gt; gt;gt; Format the date cell as DATE.
gt; gt;gt;
gt; gt;gt; Biff
gt; gt;gt;
gt; gt;gt; quot;Monaquot; gt; wrote in message
gt; gt;gt; ...
gt; gt;gt;gt;I have a worksheet example: column A is quot;datequot;, column B is quot;workquot;,
gt; gt;gt;gt;column C
gt; gt;gt;gt; is quot;amountquot;.
gt; gt;gt;gt;
gt; gt;gt;gt; 05/01/2006 test 24
gt; gt;gt;gt; 05/01/2006 12
gt; gt;gt;gt; 05/01/2006 maybe 24
gt; gt;gt;gt;
gt; gt;gt;gt; 05/02/2006 18
gt; gt;gt;gt; 05/02/2006 test 20
gt; gt;gt;gt;
gt; gt;gt;gt; I would like a formula to extract the entire row of data when quot;workquot; is
gt; gt;gt;gt; equal to test. I tried using If and Offset functions but I get blank
gt; gt;gt;gt; rows of
gt; gt;gt;gt; data (because of matching). My desired output would be:
gt; gt;gt;gt;
gt; gt;gt;gt; 05/01/2006 test 24
gt; gt;gt;gt; 05/01/2006 test 20
gt; gt;gt;gt;
gt; gt;gt;gt; Thank you.
gt; gt;gt;
gt; gt;gt;
gt; gt;
gt; gt;
gt;
gt;
gt;