Summary of shipments by month

[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

 

[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

 

[Pierre]

Don: response didn't come through. Would you take a 2nd crack at it?
Pierre

 

[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

 

[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

 

[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."