Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Monthly Usage
I have been hacking away at this for a while, and I feel that I'm at a dead
end on my own, any help is greatly appreciated. I have records of parts and the dates and quaity of usage. I am trying to total the number of each part used for a month, and then have something like a pivot table showing each part listed down, months listed across, and quantity used in the data. There is a record for each time the part is used. Here is a small sample of my data. Part # Description Issue Date TRL_QTY 401083 Head Stop Brush, Folder 11/9/05 3 401083 Head Stop Brush, Folder 11/13/05 3 401083 Head Stop Brush, Folder 2/13/06 8 401083 Head Stop Brush, Folder 2/13/06 -2 401115 Oven Gasket, P4 11/26/05 12 401137 Blade, Tidland Slitter System, Press 43 12/6/05 2 401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Monthly Usage
Hi
I reckon the pivot table is the way to go. When you create your pivot table, drag Part to the right hand side and drag Date to the top. You can then select Description as the data and use Count. Once you have done this, right-click on the date row and select Group and then select Months (as well as Year, if you like). Hope this helps Andy. "OcellNuri" wrote in message ... I have been hacking away at this for a while, and I feel that I'm at a dead end on my own, any help is greatly appreciated. I have records of parts and the dates and quaity of usage. I am trying to total the number of each part used for a month, and then have something like a pivot table showing each part listed down, months listed across, and quantity used in the data. There is a record for each time the part is used. Here is a small sample of my data. Part # Description Issue Date TRL_QTY 401083 Head Stop Brush, Folder 11/9/05 3 401083 Head Stop Brush, Folder 11/13/05 3 401083 Head Stop Brush, Folder 2/13/06 8 401083 Head Stop Brush, Folder 2/13/06 -2 401115 Oven Gasket, P4 11/26/05 12 401137 Blade, Tidland Slitter System, Press 43 12/6/05 2 401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Monthly Usage
In another sheet, list the products from A2, down, months in B1 across, and
in B2 enter =SUMPRODUCT(--(Sheet2!$B$2:$B$20=$A2),--(MONTH(Sheet2!$C$2:$C$20)=MONTH(B$1) ),Sheet2!$D$2:$D$20) and copy down and across. For the months in B1 across, eneter full dates (like 01/01/2006, 01/02/2006, etc.), just format as mmm if you want. -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "OcellNuri" wrote in message ... I have been hacking away at this for a while, and I feel that I'm at a dead end on my own, any help is greatly appreciated. I have records of parts and the dates and quaity of usage. I am trying to total the number of each part used for a month, and then have something like a pivot table showing each part listed down, months listed across, and quantity used in the data. There is a record for each time the part is used. Here is a small sample of my data. Part # Description Issue Date TRL_QTY 401083 Head Stop Brush, Folder 11/9/05 3 401083 Head Stop Brush, Folder 11/13/05 3 401083 Head Stop Brush, Folder 2/13/06 8 401083 Head Stop Brush, Folder 2/13/06 -2 401115 Oven Gasket, P4 11/26/05 12 401137 Blade, Tidland Slitter System, Press 43 12/6/05 2 401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Monthly Usage
Absolutely perfect Andy. I was trying grouping and pivot tables independent
of each other. Sometimes we make things difficult based on how difficult we expect it to be... Thanks a ton! "Andy" wrote: Hi I reckon the pivot table is the way to go. When you create your pivot table, drag Part to the right hand side and drag Date to the top. You can then select Description as the data and use Count. Once you have done this, right-click on the date row and select Group and then select Months (as well as Year, if you like). Hope this helps Andy. "OcellNuri" wrote in message ... I have been hacking away at this for a while, and I feel that I'm at a dead end on my own, any help is greatly appreciated. I have records of parts and the dates and quaity of usage. I am trying to total the number of each part used for a month, and then have something like a pivot table showing each part listed down, months listed across, and quantity used in the data. There is a record for each time the part is used. Here is a small sample of my data. Part # Description Issue Date TRL_QTY 401083 Head Stop Brush, Folder 11/9/05 3 401083 Head Stop Brush, Folder 11/13/05 3 401083 Head Stop Brush, Folder 2/13/06 8 401083 Head Stop Brush, Folder 2/13/06 -2 401115 Oven Gasket, P4 11/26/05 12 401137 Blade, Tidland Slitter System, Press 43 12/6/05 2 401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Finding Monthly Usage
Thanks for the feedback - glad you're sorted.
Andy. "OcellNuri" wrote in message ... Absolutely perfect Andy. I was trying grouping and pivot tables independent of each other. Sometimes we make things difficult based on how difficult we expect it to be... Thanks a ton! "Andy" wrote: Hi I reckon the pivot table is the way to go. When you create your pivot table, drag Part to the right hand side and drag Date to the top. You can then select Description as the data and use Count. Once you have done this, right-click on the date row and select Group and then select Months (as well as Year, if you like). Hope this helps Andy. "OcellNuri" wrote in message ... I have been hacking away at this for a while, and I feel that I'm at a dead end on my own, any help is greatly appreciated. I have records of parts and the dates and quaity of usage. I am trying to total the number of each part used for a month, and then have something like a pivot table showing each part listed down, months listed across, and quantity used in the data. There is a record for each time the part is used. Here is a small sample of my data. Part # Description Issue Date TRL_QTY 401083 Head Stop Brush, Folder 11/9/05 3 401083 Head Stop Brush, Folder 11/13/05 3 401083 Head Stop Brush, Folder 2/13/06 8 401083 Head Stop Brush, Folder 2/13/06 -2 401115 Oven Gasket, P4 11/26/05 12 401137 Blade, Tidland Slitter System, Press 43 12/6/05 2 401138 Blade, Tidland Slitter System, Press 43 POWDER 6/1/05 2 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding last used | Excel Worksheet Functions | |||
Charting Utility Usage | Charts and Charting in Excel | |||
Convert irregular data to monthly equivalent? | Excel Discussion (Misc queries) | |||
Making list of items to truck monthly usage | Excel Discussion (Misc queries) | |||
How do I create a report to monitor monthly utilities | Excel Worksheet Functions |