This is a multi-part message in MIME format.
------=_NextPart_000_1678_01C6D3FA.BA74F670
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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.
=20
Count of ticket month =20
application ticket 2006-06 2006-07 2006-08 =20
ABC T1 1 =20
T2 1 =20
T4 1=20
XYZ T3 1 =20
T5 1=20
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.
--=20
Regards
Roger Govier
"njoy" <nitinpjain[ at ]gmail.com> wrote in message =
news:1157767543.675130.310570[ at ]m73g2000cwd.googlegroups.com...
[Quoted Text] >I would like to create a report using following sample data in excel
>=20
> ticket month application
> T1 2006-06 ABC
> T2 2006-06 ABC
> T3 2006-07 XYZ
> T4 2006-08 ABC
> T5 2006-08 XYZ
>=20
>=20
> using this data I would like to create following report (application
> roadmap)
> i.e. which ticket would be implemented in which monthly release
>=20
>=20
> Timeline 2006-06 2006-07 2006-08
> application
> ABC T1 , T2 T4
> XYZ T3 T5
>=20
> is it possible ?
>=20
> please let me know
>=20
> Thanks in advance.
>=20
> - NJ
>
------=_NextPart_000_1678_01C6D3FA.BA74F670
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2963" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT size=3D2>Hi</FONT></DIV>
<DIV><FONT size=3D2>You cannot achieve exactly what you want from a =
Pivot Table.=20
It is not possible for the PT to show two items of data in the same =
cell,=20
separated by a comma, neither would a formula solution produce this type =
of=20
result. You would need to use a VBA solution.</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>However, you could achieve a layout as below from a =
Pivot=20
Table if that is acceptable.</FONT></DIV>
<DIV><FONT size=3D2> </DIV>
<DIV>
<TABLE style=3D"WIDTH: 319pt; BORDER-COLLAPSE: collapse" cellSpacing=3D0 =
cellPadding=3D0 width=3D425 border=3D0 x:str>
<COLGROUP>
<COL style=3D"WIDTH: 91pt; mso-width-source: userset; mso-width-alt: =
4425"=20
width=3D121>
<COL style=3D"WIDTH: 62pt; mso-width-source: userset; mso-width-alt: =
3035"=20
span=3D2 width=3D83>
<COL style=3D"WIDTH: 52pt; mso-width-source: userset; mso-width-alt: =
2523"=20
span=3D2 width=3D69>
<TBODY>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; WIDTH: 91pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
width=3D121 height=3D17></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; WIDTH: 62pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
width=3D83></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; WIDTH: 62pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
width=3D83></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; WIDTH: 52pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
width=3D69></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; WIDTH: 52pt; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
width=3D69></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft height=3D17><FONT size=3D2>Count of=20
ticket </FONT></TD>
<TD class=3Dxl23=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft><FONT=20
size=3D2>month </FONT></TD>
<TD class=3Dxl23=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl24=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt =
solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft height=3D17><FONT size=3D2>application</FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft><FONT=20
=
size=3D2>ticket </FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft><FONT =
size=3D2> 2006-06 </FONT></TD>
<TD class=3Dxl27=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dleft><FONT =
size=3D2>2006-07 </FONT></TD>
<TD class=3Dxl29=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt =
solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dleft><FONT =
size=3D2>2006-08 </FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft height=3D17><FONT size=3D2>ABC</FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft><FONT=20
=
size=3D2>T1 &n=
bsp;</FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dright x:num><FONT size=3D2>1</FONT></TD>
<TD class=3Dxl27=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl29=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt =
solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl25=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
height=3D17><FONT size=3D2> </FONT></TD>
<TD class=3Dxl28=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dleft><FONT=20
=
size=3D2>T2 &n=
bsp;</FONT></TD>
<TD class=3Dxl28=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dright x:num><FONT size=3D2>1</FONT></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"><FONT=20
size=3D2></FONT></TD>
<TD class=3Dxl30=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl25=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
height=3D17><FONT size=3D2> </FONT></TD>
<TD class=3Dxl28=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dleft><FONT=20
=
size=3D2>T4 &n=
bsp;</FONT></TD>
<TD class=3Dxl28=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: transparent"><FONT=20
size=3D2></FONT></TD>
<TD class=3Dxl30=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dright x:num><FONT size=3D2>1</FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft height=3D17><FONT size=3D2>XYZ</FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"=20
align=3Dleft><FONT=20
=
size=3D2>T3 &n=
bsp;</FONT></TD>
<TD class=3Dxl22=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #e0dfe3; =
BACKGROUND-COLOR: transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl27=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: black 0.5pt solid; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"=20
align=3Dright x:num><FONT size=3D2>1</FONT></TD>
<TD class=3Dxl29=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt =
solid; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD></TR>
<TR style=3D"HEIGHT: 12.75pt" height=3D17>
<TD class=3Dxl26=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; =
BACKGROUND-COLOR: transparent"=20
height=3D17><FONT size=3D2> </FONT></TD>
<TD class=3Dxl31=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: =
transparent"=20
align=3Dleft><FONT=20
=
size=3D2>T5 &n=
bsp;</FONT></TD>
<TD class=3Dxl31=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl32=20
style=3D"BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: =
#e0dfe3; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: =
transparent"><FONT=20
size=3D2> </FONT></TD>
<TD class=3Dxl33=20
style=3D"BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #e0dfe3; =
BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: black 0.5pt solid; =
BACKGROUND-COLOR: transparent"=20
align=3Dright x:num><FONT =
size=3D2>1</FONT></TD></TR></TBODY></TABLE></DIV>
<DIV> </DIV>
<DIV>Set up your PT and drag Application followed by Ticket to the Row=20
Area</DIV>
<DIV>Drag Month to the Column Area</DIV>
<DIV>Drag Count of Ticket to the Data area.</DIV>
<DIV>Double click on Application, and set Subtotals to None</DIV>
<DIV>From the PT toolbar, choose Table Options and switch off Grand =
Total for=20
Rows and Grand Total for Columns.</DIV></FONT>
<DIV><BR><FONT size=3D2>-- <BR>Regards</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>Roger Govier</FONT></DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2></FONT> </DIV>
<DIV><FONT size=3D2>"njoy" <</FONT><A =
href=3D"mailto:nitinpjain[ at ]gmail.com"><FONT=20
size=3D2>nitinpjain[ at ]gmail.com</FONT></A><FONT size=3D2>> wrote in =
message=20
</FONT><A=20
href=3D"news:1157767543.675130.310570[ at ]m73g2000cwd.googlegroups.com"><FONT=
=20
size=3D2>news:1157767543.675130.310570[ at ]m73g2000cwd.googlegroups.com</FONT=
></A><FONT=20
size=3D2>...</FONT></DIV><FONT size=3D2>>I would like to create a =
report using=20
following sample data in excel<BR>> <BR>>=20
ticket =20
month application<BR>>=20
T1 =20
2006-06 ABC<BR>>=20
T2 =20
2006-06 ABC<BR>>=20
T3 =20
2006-07 XYZ<BR>>=20
T4 =20
2006-08 ABC<BR>>=20
T5 =20
2006-08 XYZ<BR>> <BR>> <BR>> using this =
data I=20
would like to create following report (application<BR>> =
roadmap)<BR>> i.e.=20
which ticket would be implemented in which monthly release<BR>> =
<BR>>=20
<BR>> Timeline =
2006-06 =20
2006-07 2006-08<BR>> application<BR>>=20
ABC &nbs=
p; T1 ,=20
T2 =
; =20
T4<BR>>=20
XYZ &nbs=
p; =
; =20
T3 =
;=20
T5<BR>> <BR>> is it possible ?<BR>> <BR>> please let me =
know<BR>>=20
<BR>> Thanks in advance.<BR>> <BR>> - =
NJ<BR>></FONT></BODY></HTML>
------=_NextPart_000_1678_01C6D3FA.BA74F670--
|