Horizontal weekly date values aggregated into single vertical monthly value

[sgray]

I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.Here's the before picture:column A column B column C column D column E column FPart num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A 100 100 100 100 100B 150 150 150 150 150C 200 200 200 200 200D 250 250 250 250 250The output after manually doing this would look like this:column A column B column CPart num Date QuantityA Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250Thanks for your help on this one. I'm interested to see what the community has to say (sorry about the format...)sgray

 

[schielrn]

Welcome to the Board!

Sorry I can't really decipher that, but you can copy and paste directly from Excel to the board. Sometimes it is easy to just put borders around the cells to make it a little more visual.

 

[sgray]

Hi schielrn, let me try this again.

I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.Here's the before picture

Part num	1/1/2013	1/8/2013	1/15/2013	1/22/2013	1/29/2013
A	100	100	100	100	100
B	150	150	150	150	150
C	200	200	200	200	200
D	250	250	250	250	250

<tbody>
</tbody>

The output after manually doing this would look like this

Part num	Date	Quantity
A	Jan-13	500
B	Jan-13	750
C	Jan-13	1000
D	Jan-13	1250

<tbody>
</tbody>

Thanks for your help.

sgray

 

[schielrn]

Assuming this needs to be dynamic as far as which row the part number is in, this is one way:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Part num	1/1/2013	1/8/2013	1/15/2013	1/22/2013	1/29/2013
2	A	100	100	100	100	100
3	B	150	150	150	150	150
4	C	200	200	200	200	200
5	D	250	250	250	250	250
6
7
8	Part num	Date
9	A	Jan-13	500
10	B	Jan-13	750
11	C	Jan-13	1000
12	D	Jan-13	1250
Sheet2

#VALUE!

Hope that helps.

 

[sgray]

schielrn, thanks for the quick reply. Assuming the only input data points you have are in cells A1 - F5, is there a way to to dynamically populate columns A9 - C12 in your example, without manually typing any of the values (ie. part nums or dates)?

Thanks, sgray

 

[schielrn]

It could probably be done dynamically with using combinations of the frequency formula with other formulas to create a unique list, but I have only seen it done and have not really tried to attempt it myself. The data isn't set up to lend itself to a pivot table, so in short it could definitely be done with VBA and possibly with formulas, but nothing that I have time to work on at the moment. I would copy and paste the list of part numbers and remove duplicates and then put the dates in.