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

[TABLE="width: 369"]
<tbody>[TR]
[TD="class: xl66, width: 75, bgcolor: transparent"]Part num
[/TD]
[TD="class: xl67, width: 90, bgcolor: transparent"]1/1/2013
[/TD]
[TD="class: xl67, width: 78, bgcolor: transparent"]1/8/2013
[/TD]
[TD="class: xl67, width: 85, bgcolor: transparent"]1/15/2013
[/TD]
[TD="class: xl67, width: 84, bgcolor: transparent"]1/22/2013
[/TD]
[TD="class: xl67, width: 78, bgcolor: transparent"]1/29/2013
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]A
[/TD]
[TD="class: xl68, bgcolor: transparent"]100
[/TD]
[TD="class: xl68, bgcolor: transparent"]100
[/TD]
[TD="class: xl68, bgcolor: transparent"]100
[/TD]
[TD="class: xl68, bgcolor: transparent"]100
[/TD]
[TD="class: xl68, bgcolor: transparent"]100
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]B
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[TD="class: xl68, bgcolor: transparent"]150
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]C
[/TD]
[TD="class: xl68, bgcolor: transparent"]200
[/TD]
[TD="class: xl68, bgcolor: transparent"]200
[/TD]
[TD="class: xl68, bgcolor: transparent"]200
[/TD]
[TD="class: xl68, bgcolor: transparent"]200
[/TD]
[TD="class: xl68, bgcolor: transparent"]200
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]D
[/TD]
[TD="class: xl68, bgcolor: transparent"]250
[/TD]
[TD="class: xl68, bgcolor: transparent"]250
[/TD]
[TD="class: xl68, bgcolor: transparent"]250
[/TD]
[TD="class: xl68, bgcolor: transparent"]250
[/TD]
[TD="class: xl68, bgcolor: transparent"]250
[/TD]
[/TR]
</tbody>[/TABLE]

The output after manually doing this would look like this

[TABLE="width: 244"]
<tbody>[TR]
[TD]Part num
[/TD]
[TD]Date
[/TD]
[TD]Quantity
[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]Jan-13
[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]B
[/TD]
[TD]Jan-13
[/TD]
[TD]750
[/TD]
[/TR]
[TR]
[TD]C
[/TD]
[TD]Jan-13
[/TD]
[TD]1000
[/TD]
[/TR]
[TR]
[TD]D
[/TD]
[TD]Jan-13
[/TD]
[TD]1250
[/TD]
[/TR]
</tbody>[/TABLE]

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.