Summary of shipments by month


P

Pierre

Have numerous items with Different Start Months, and different annual
sales quantites, and different years.

Need to create a summary that shows the Month and Year for the
shipments. The annual quantity is spread out evenly into the 12
months.
If a program begins in August 2010 (they all run 12 months) and the
annual quantity is 120, then the summary would read
Aug 2010. Sep 2010. Oct 2010. Nov 2010 Dec 2010 Jan 2011 Feb 2011 etc
10 10 10 10
10 10 10

Then year 2 gets populated with an annual quantity of 360, and starts
the month after year 1 is completed, with 30 units shipped per month
for year 2.
Have 7 years to populate with different quantities.

Source data:
A16: 8/12/2010
B16: East
E16(year 1 quantity): 120
F15(year 2 quantity): 360
G16(year 3 quantity): 240
etc.

On a Date label in Row 1, need to populate each month with the
shipment quantity, located in row 2
I've got the program dates broken into start month, and start year
into separate cells in helper columns.
Thank you for any ideas for an approach on this.
Pierre
 
Ad

Advertisements

D

Don Guillett Excel MVP

Have numerous items with Different Start Months, and different annual
sales quantites, and different years.

Need to create a summary that shows the Month and Year for the
shipments.   The annual quantity is spread out evenly into the 12
months.
If a program begins in August 2010 (they all run 12 months) and the
annual quantity is 120, then the summary would read
Aug 2010. Sep 2010. Oct 2010. Nov 2010 Dec 2010 Jan 2011 Feb 2011 etc
10               10               10           10
10              10        10

Then year 2 gets populated with an annual quantity of 360, and starts
the month after year 1 is completed, with 30 units shipped per month
for year 2.
Have 7 years to populate with different quantities.

Source data:
A16: 8/12/2010
B16: East
E16(year 1 quantity): 120
F15(year 2 quantity): 360
G16(year 3 quantity): 240
etc.

On a Date label in Row 1, need to populate each month with the
shipment quantity, located in row 2
I've got the program dates broken into start month, and start year
into separate cells in helper columns.
Thank you for any ideas for an approach on this.
Pierre
 
A

Anand

Hi,

This can be done using a pivot table in the column headers put date
column and in sum area you can put the columns E, F and G.
For creating pivot tables every column should have names which you
seem to have and you should have your data in columns which also seems
to be correctly arranged in your case.
In case solution is not very clear feel free to reach me at
(e-mail address removed)
Hope that helps,
Regards,
Anand Kumar
 
P

Pierre

Found a way to do it. (It was messy)
Created a column with start and stop dates for each year.
Created a large table to the right for each year with column labels
containing the yr/month for all years, in this case through 2020.
Entered a formula asking if the column label is between the start and
stop date for that item. If so, divide that yearly quantity by 12, if
not, leave blank.
Created the summary sheet titled by month, quarter and year, which
looks at each column in the tables, and adds up anything that
appears. Here, we can get summaries by month, quarter, and year.

Thank you for any effort expended.

Pierre
 
Ad

Advertisements

D

Don Guillett Excel MVP

Found a way to do it. (It was messy)
Created a column with start and stop dates for each year.
Created a large table to the right for each year with column labels
containing the yr/month for all years, in this case through 2020.
Entered a formula asking if the column label is between the start and
stop date for that item.  If so, divide that yearly quantity by 12, if
not, leave blank.
Created the summary sheet titled by month, quarter and year, which
looks at each column in the tables, and adds up anything that
appears.  Here, we can get summaries by month, quarter, and year.

Thank you for any effort expended.

Pierre





- Show quoted text -

"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top