Consolidate Item, months and volume
 

I can turn the dates to months in TABLE 1 with =text (b2;"mmm.yy"), but how
do I consolidate the Item, months and volume, so I get TABLE 2:

TABLE 1

Item Date Volume
A 01.04.2010 10 000
A 01.04.2010 10 200
B 01.04.2010 9 800
A 15.04.2010 10 500
B 01.05.2010 9 750
B 01.05.2010 10 150
A 01.06.2010 10 200
B 01.07.2010 9 850


TABLE 2

Item Month Volume
A apr.10 30 700
B apr.10 9 800
B mai.10 19 900
A jun.10 10 200
B jul.10 9 850

--
Regards
Tommy

Consolidate Item, months and volume
 

Tommy,

Detach/open workbook "*AdvancedFilter Unique - ConsolidateData - Tommy
- SDG11.xls*".

Then run the "*ConsolidateData*" macro.


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: AdvancedFilter Unique - ConsolidateData - Tommy - SDG11.xls|
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=528|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194629

http://www.thecodecage.com/forumz

Consolidate Item, months and volume
 

Thank you very much. Exactly what I was looking for.
Interesting site you have.
--
Regards
Tommy


"stanleydgromjr" wrote:


Tommy,

Detach/open workbook "*AdvancedFilter Unique - ConsolidateData - Tommy
- SDG11.xls*".

Then run the "*ConsolidateData*" macro.


Have a great day,
Stan


+-------------------------------------------------------------------+
|Filename: AdvancedFilter Unique - ConsolidateData - Tommy - SDG11.xls|
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=528|
+-------------------------------------------------------------------+

--
stanleydgromjr
------------------------------------------------------------------------
stanleydgromjr's Profile: 503
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=194629

http://www.thecodecage.com/forumz

.

Consolidate Item, months and volume
 

Hi Tommy

as an alternative to Stanley's solution, you could just use a Pivot Table

Assuming XL2003
Place cursor within your source data rangeDataCreate Listclick my
list has headers
DataPivot TableFinish
On the PT skeleton that appears on a new page
Drag Item to the Row area
Drag Date to the Row area
Drag Volume to the Data area

Right click on any date valueGroup and OutlineGroupMonths

By Creating the list beforehand, the data source for your PT Report will
grow as you add more lines.
After adding more data, right click on the PTRefresh and your new data
will be included.

--
Regards
Roger Govier

Tommy wrote:
I can turn the dates to months in TABLE 1 with =text (b2;"mmm.yy"), but how
do I consolidate the Item, months and volume, so I get TABLE 2:

TABLE 1

Item Date Volume
A 01.04.2010 10 000
A 01.04.2010 10 200
B 01.04.2010 9 800
A 15.04.2010 10 500
B 01.05.2010 9 750
B 01.05.2010 10 150
A 01.06.2010 10 200
B 01.07.2010 9 850


TABLE 2

Item Month Volume
A apr.10 30 700
B apr.10 9 800
B mai.10 19 900
A jun.10 10 200
B jul.10 9 850