Hi,

How can I use pivot table to reflect my data table in following
manner:

DATA TABLE
item serial
abc 110
abc 111
abc 112
abc 114
def 760
def 761
def 762

PIVOT TABLE
---------------------------------------
item countofserial start_serial end_serial
abc 3 110 112
abc 1 114 114
def 3 760 762


Can calculated fields help?
But how do I use em to show the actual serial also in pivot & also
pivot will club 'abc' item as 4 & not 3 & 1 as there is a serial
missing in sequence.


Thanks.

Hi,

This really is not a pivot table problem, you should use a formula approach

you can use =COUNTIF(ItemRange,Item) to get the totals for each item
you can use the =MAX(IF(ItemRange=Item,SerialRange,""))
and =MIN(IF(ItemRange=Item,SerialRange,""))

These last two are array formulas so you press Shift+Ctrl+Enter to enter them.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"Angela" wrote:

Hi,

How can I use pivot table to reflect my data table in following
manner:

DATA TABLE
item serial
abc 110
abc 111
abc 112
abc 114
def 760
def 761
def 762

PIVOT TABLE
---------------------------------------
item countofserial start_serial end_serial
abc 3 110 112
abc 1 114 114
def 3 760 762


Can calculated fields help?
But how do I use em to show the actual serial also in pivot & also
pivot will club 'abc' item as 4 & not 3 & 1 as there is a serial
missing in sequence.


Thanks.