How to count unique value in Excel Pivot Table 1

[imme]

Hi,

I am stuck here in counting unique values in many-many relationship. Here is situation.

I have three columns in my excel sheet, first for patent numbers, second for Product code for each patent and third is group no. for each product code.
Data have one patent number to many product code (which could be part of one group) and vies-a-versa.

I have to return a report, which shows that for each group, how many unique patents are there.

In pivot table it simply count number of entry but not the unique count. Can you help with the solution?

Thank you

 

[ETID]

Could you post an example of the raw data and the format you expect the pivot to show?

 

[imme]

group Count of Patent no
code 1 2 3 4 Grand Total
----------------------------------------------------
1.002.002 1 1
1.002.003 1 1
1.003.003 1 1
1.003.004 1 1
1.004.002 1 1
2.002.000 4 4
2.002.002 6 6
3.006.000 2 2
3.007.000 5 5
3.007.001 2 2
3.007.002 2 2
4.000.000 3 3
4.001.000 1 1
4.001.001 1 1
4.001.002 2 2
4.004.004 1 1
Grand Total 5 10 11 8 34
unique numbers 3 7 6 2 18

I need the last row... rest of the thing is what i can find right now.

Thank you.

 

[ETID]

Unique numbers? .... I'm built too low for your example (it's over my head), I see the 3 unique numbers for group 1
but I don't follow the 7 for group 2 ?

 

[macrus]

This may help.
Sort your data by the first column (patent number). Then in a new column enter a 1 on the first row. In the second row enter the following formula (assume patent number is in column A):

IF(A2=A1,0,1)

Copy this formula in each row of the new column down to the last row of your data. The pivot table can now simply sum this column to get a unique count.

Repeat this process for other columns you need to count uniquely.

Macrus