Pivot table: how to count unique? need help!

[Guest]

this a table similar to the one I really have
serialnb label
111 a
111 b
222 a

I want to create a pivot that shows how many *unique* serials exist, and
also per label, etc.
Doing the "usual" pivot I get the following

Count of serialnb
serialnb label Total
111 a 1
b 1
222 a 1
Grand Total 3

How can I show the results I want? (should be 2 for unique serial numbers)
Many thanks!
luis

 

[Paul Lautman]

this a table similar to the one I really have
serialnb label
111 a
111 b
222 a

I want to create a pivot that shows how many *unique* serials exist,
and also per label, etc.
Doing the "usual" pivot I get the following

Count of serialnb
serialnb label Total
111 a 1
b 1
222 a 1
Grand Total 3

How can I show the results I want? (should be 2 for unique serial
numbers) Many thanks!
luis

Drop serialnb into ROW area
Drop label into ROW area
Drop label into DATA area
That gives me

serialnb label Total
111 a 1
b 1
111 Total 2
222 a 1
222 Total 1
Grand Total 3

 

[Guest]

Hi Paul,
thanks. That doesn't give the right answer though... i.e. would still see
that in total there are 3 devices (those are serial numbers of the devices)
but it is not true... there are 2 devices with distinct serial numbers and
happen that one has 2 lables attached and the other one...

luis

 

[Paul Lautman]

Hi Paul,
thanks. That doesn't give the right answer though... i.e. would still
see that in total there are 3 devices (those are serial numbers of
the devices) but it is not true... there are 2 devices with distinct
serial numbers and happen that one has 2 lables attached and the
other one...

luis

I can offer 2 methods of doing this. One uses 2 tables, with the second
table based on the resoults of the first one.
The other uses a simple COUNTA() function to supply the result.

Both methds are a bit difficult to explain, so I'll pop an example on the
web for you.

 

[Guest]

I can offer 2 methods of doing this. One uses 2 tables, with the second
table based on the resoults of the first one.
The other uses a simple COUNTA() function to supply the result.

Both methds are a bit difficult to explain, so I'll pop an example on the
web for you.

Hi Paul,
ok. thanks. please let me know where I can see that.
I used COUNTA... but not a good solution as I want it to dinamically change
when i change the variables of the pivot.

thanks
luis

 

[Paul Lautman]

Hi Paul,
ok. thanks. please let me know where I can see that.
I used COUNTA... but not a good solution as I want it to dinamically
change when i change the variables of the pivot.

thanks
luis

See http://cjoint.com/?gouq44q3Mc
The COUNTA call will automatically change to take account of varying numbers
of serials

 

[Debra Dalgleish]

You can add a field to the source data, and use it to calculate the
unique count in the pivot table. There's an example here:

http://www.contextures.com/xlPivot07.html#Unique

 

[Guest]

PERFECT!!!!!
that is exactly what I had asked!

great and excellent site!
thanks Debra

luis

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.