Pivot Table Excel 2007

[GPnew]

We have 4 trading companies who often sell to the same Customer
We combine the debtors reports though a pivot table in excel to understand each customers total debt with us. The pivot table shows the amount the customer owes to each company and then sums the group. All that work fine. But we also like to know when they last paid and the amount of that payment
In the summary get the late date using max last date so that s OK. My question is how to show the amount of the payment associated with the latest date in the summary. (as opposed to the max ,min or sum of the latest amount field). In the example $100 is correctly the amount of the payment on the 12/Aug/12 and is based on the date field NOT by summing , or taking the max or min etc of the Amount of last payment field. Can a pivot table be used to show this? Thanks
EG
Customer Sum Outstading Date of Last Pmt Amt of Last Pmt
Big Buyer $1000 12/Aug/12 $100 Our Company 1 $250 12/Aug/12 $100
Our Cpmany 2 $750 12/July/12 %500

 

[SkipVought]

hi,

Your example is unintelligible! You've been around Tek-Tips long enought to use TGML tags, yes? I ASSUME that you mean...

Customer     Sum Outstading  Date of Last Pmt  Amt of Last Pmt
Big Buyer             $1000         12/Aug/12             $100 
Our Company 1          $250         12/Aug/12             $100
Our Cpmany 2           $750        12/July/12             $500

One way would be to 1) SORT the source data table by Customer, Pmt Date DESC, and then use INDEX() & MATCH() to return the Pmt associated with the most recent Pmt Date, which would occur as the FIRST row for any Customer.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}