Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
extract and append data to a template | Excel Discussion (Misc queries) | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Data extract from access query | Excel Discussion (Misc queries) | |||
Line Graph Data Recognition | Charts and Charting in Excel |