Pivot table and unique entries

Posted by Michael Leischel on October 23, 2000 2:39 AM

I use Excel 2000 on Windows NT 4.0 and I have a problem with pivot tables.

I want to count the unique field items in a pivot table. How can I manage this?

My pivot table example

AU PO Value
AU1 PO1 200
AU1 PO2 100
AU1 PO3 40
AU1 PO4 30 This is the data source
AU2 PO1 200
AU2 PO2 100
AU3 PO1 200
AU3 PO2 100
AU3 PO3 40
AU3 PO4 30

Grouping PO in two groups
(Group1 is PO1 and PO2, Group2 is PO3 and PO4)
and counting AU will deliver the following pivot table:

PO2 Value Count - AU
Group1 900 6
Group2 140 4
Total 1040 10

So far, so good. But the result I want to get is :

PO2 Value 'UNIQUE Count - AU'
Group1 900 3
Group2 140 2
Total 1040 3

There are 3 unique entries from AU in Group1.
There are 2 unique entries from AU in Group2.
There are 3 unique entries from AU in Total.

Who can help me?

Thanks in advance.

Posted by thomas venn on October 23, 2000 2:25 PM

you might want to add another field to Concatenate your data so that you would have "unique data"
for example: AU1PO1 is the result of this formula =A9&""&B9
by doing this, you would have an "unique" AU. however, it sounds like you need more than this, because you have PO2 in group1 as well, so what you can do is add another column, and say: =if(a1="po1" or a1="po2", then "group1"). At this point, you can concatenate group1 and AU to get your results.

cheers,

Thomas