Return result only if data complies.
Hi, I am trying to do some analysis, what I need is to extract data from a
file, but only the data that meets criteria, example, I want to extract data from a file that shows orders with their respective delivery dates, I want to extract the data that meets the following Sheet 1 A B C 1 Product Qty Ship Date 2 Blue Pens 10 10/11/2005 3 Black pens 25 31/10/2005 4 Blue Pens 50 28/10/2005 5 Red pens 20 01/11/2005 6 Blue Pens 25 27/10/2005 In another tab I want to return the data that has already passed its Ship Date, so the answer to the above would be. In col A I want the data to return the product that is passed it's Ship date and in col B I want the data to return the sum of those products that are passed their Ship date. Sheet 2 A B 1 Blue Pens 75 2 Black pens 25 3 Red Pens 20 |
Return result only if data complies.
I would just list the products in column A, and then use
=SUMPRODUCT(--(Sheet1!A2:A200=A1),--(Sheet1!C2:C200<TODAY()),Sheet1!B2:B200) it will show zeroes as well, but you could use conditional formatting to highlight the ones that are not zero -- HTH RP (remove nothere from the email address if mailing direct) "John Moore" wrote in message ... Hi, I am trying to do some analysis, what I need is to extract data from a file, but only the data that meets criteria, example, I want to extract data from a file that shows orders with their respective delivery dates, I want to extract the data that meets the following Sheet 1 A B C 1 Product Qty Ship Date 2 Blue Pens 10 10/11/2005 3 Black pens 25 31/10/2005 4 Blue Pens 50 28/10/2005 5 Red pens 20 01/11/2005 6 Blue Pens 25 27/10/2005 In another tab I want to return the data that has already passed its Ship Date, so the answer to the above would be. In col A I want the data to return the product that is passed it's Ship date and in col B I want the data to return the sum of those products that are passed their Ship date. Sheet 2 A B 1 Blue Pens 75 2 Black pens 25 3 Red Pens 20 |
Return result only if data complies.
Hi Bob, not quite what I need, I was already using SUMPRODUCT, but wondered
if there was a way to return ONLY the data that was passed its Ship date for both the product and the sum of the qty. "Bob Phillips" wrote: I would just list the products in column A, and then use =SUMPRODUCT(--(Sheet1!A2:A200=A1),--(Sheet1!C2:C200<TODAY()),Sheet1!B2:B200) it will show zeroes as well, but you could use conditional formatting to highlight the ones that are not zero -- HTH RP (remove nothere from the email address if mailing direct) "John Moore" wrote in message ... Hi, I am trying to do some analysis, what I need is to extract data from a file, but only the data that meets criteria, example, I want to extract data from a file that shows orders with their respective delivery dates, I want to extract the data that meets the following Sheet 1 A B C 1 Product Qty Ship Date 2 Blue Pens 10 10/11/2005 3 Black pens 25 31/10/2005 4 Blue Pens 50 28/10/2005 5 Red pens 20 01/11/2005 6 Blue Pens 25 27/10/2005 In another tab I want to return the data that has already passed its Ship Date, so the answer to the above would be. In col A I want the data to return the product that is passed it's Ship date and in col B I want the data to return the sum of those products that are passed their Ship date. Sheet 2 A B 1 Blue Pens 75 2 Black pens 25 3 Red Pens 20 |
Return result only if data complies.
How about an alternative?
Add another column to your data: Past Ship =c2today() and drag down. You'll end up with a column of Trues/falses. Then create a pivottable based on your data. Select the range. data|pivottable follow the wizard until you get to the dialog with a layout button on it. Click that layout button Drag the past ship button to the page field drag the product button to the row field drag the qty button to the data field (if it says "count of qty", double click on it and change it to Sum.) Finish up. On the page field, you can choose True/False or All to see your report. If your list grows, you may want to create a dynamic range so that you don't have to rebuild the pivottable each time it grows/contracts--you can just rightclick on the pivottable and choose refresh instead. See Debra Dalgeish's site for some nice tips: http://contextures.com/xlNames01.html#Dynamic John Moore wrote: Hi, I am trying to do some analysis, what I need is to extract data from a file, but only the data that meets criteria, example, I want to extract data from a file that shows orders with their respective delivery dates, I want to extract the data that meets the following Sheet 1 A B C 1 Product Qty Ship Date 2 Blue Pens 10 10/11/2005 3 Black pens 25 31/10/2005 4 Blue Pens 50 28/10/2005 5 Red pens 20 01/11/2005 6 Blue Pens 25 27/10/2005 In another tab I want to return the data that has already passed its Ship Date, so the answer to the above would be. In col A I want the data to return the product that is passed it's Ship date and in col B I want the data to return the sum of those products that are passed their Ship date. Sheet 2 A B 1 Blue Pens 75 2 Black pens 25 3 Red Pens 20 -- Dave Peterson |
All times are GMT +1. The time now is 11:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com