Unique records in Pivot Tables

  • Thread starter Thread starter Helen T
  • Start date Start date
H

Helen T

Excel 2000

I have data that stores Client Invitation to Tender and
Supplier Contract details. Each Client ITT is uniquely
identified. However we send out the same ITT to many
suppliers so each request for service is given the same
identifier, eg

Reference Client/Supplier Contract Name Value Status
AC/001 Client blah 4,000 pend
AC/002 Client blah2 5,000 pend
AS/001 Supplier blah3 3,000 pend
AS/001 Supplier blah3 3,000 pend

When I create my table by Client to get the total value
pending the result is 9,000 which is correct.

However, when I do the same for Supplier the total value
pending is 6,000 which is incorrect, I need it to be 3,000.

Any help is greatly appreciated.

H
 
How about adding another column to your raw data:

I put a nice header in row 1 and this in row 2:

=IF(COUNTIF(A$2:A2,A2)=1,D2,0)
And dragged down.

It picks up the value from the first reference in column A. Duplicates are made
0.

Then include that in your pivottable.
 
Dave

Thanks for this. I just changed the D2 to A2 (as there
will invariably be multiple contracts with the same value).

Worked a treat!

Thanks again
 
Back
Top