Count unique values in pivot table

[katy1985]

Is there a way to count unique values in a pivot table? I want to determine if a batch code of stock has more than one expiry date (which it shouldnt) so if the pivot counted more than one it would highlight an error?

[judgeh59]

If I understand your question, the answer is yes....that is exactly what PT do....depending how your data is set up....can you upload a "clean" set of data?....thanks....

[katy1985]

Sorry i dont think ive explained very well...

If my data looks something like this:

N028 B1002B02 31-Jul-2013 96
N028 B1002B02 31-Aug-2011 16
N028 B1002B02 31-Aug-2011 32

I want to produce a pivot by batch number (B*****) and sell by date so it only shows the 2 differnet dates. At the minute, if i pivot this it will show:

B1002B02 3

Because theres 3 dates, but i want it show 2 because there are only 2 different dates?

[JosephP]

you would need to use the PowerPivot add-in which can create a distinct count-regular pivot tables (still!) cannot do that. or add a formula to the raw data to calculate the distinct count

[Pepe Le Mokko]

Or add a column to your data source containing =1/countif($a$1:$a$100,$a1) where col A contains the batch numbers.
Include this column in the PT in the Data field using sum.If the result of a batch number is larger than 1 it is not unique ( you will have to round the reults probably)