pivot table question for Excel gurus .. seems difficult.

[njoy]

I would like to create a report using following sample data in excel

ticket month application
T1 2006-06 ABC
T2 2006-06 ABC
T3 2006-07 XYZ
T4 2006-08 ABC
T5 2006-08 XYZ


using this data I would like to create following report (application
roadmap)
i.e. which ticket would be implemented in which monthly release


Timeline 2006-06 2006-07 2006-08
application
ABC T1 , T2 T4
XYZ T3 T5

is it possible ?

please let me know

Thanks in advance.

- NJ

 

[Roger Govier]

Hi
You cannot achieve exactly what you want from a Pivot Table. It is not possible for the PT to show two items of data in the same cell, separated by a comma, neither would a formula solution produce this type of result. You would need to use a VBA solution.

However, you could achieve a layout as below from a Pivot Table if that is acceptable.


Count of ticket month
application ticket 2006-06 2006-07 2006-08
ABC T1 1
T2 1
T4 1
XYZ T3 1
T5 1


Set up your PT and drag Application followed by Ticket to the Row Area
Drag Month to the Column Area
Drag Count of Ticket to the Data area.
Double click on Application, and set Subtotals to None
From the PT toolbar, choose Table Options and switch off Grand Total for Rows and Grand Total for Columns.