pivot tables - show one entry from a group

[EricKei]

I have a question about pivot tables--

I have a dataset in Excel with the columns NAME, DEBIT, CREDIT, and BALANCE.
The info was originally drawn from a QuickBooks database. Debit and credit
are from individual entries, and I have them Summed. However, Balance is a
"running balance" field in QBooks, but, when I put it into a pivot table, I
cannot find a way to get ti to show only the LAST entry for a given grouping.
For instance, this is a sample of the data before converting it to a ptable:

Name Debit Credit Balance
1301 ROCHEBLAVE 0.00 10,372,408.47
1301 ROCHEBLAVE 1,425.00 10,373,833.47
1302 Ferry Place 1,510.00 9,390,142.17
1304-06 Ferry 500.00 10,015,240.94
1304-06 Ferry 300.00 9,650,143.41
1308-10 Ferry 500.00 10,014,740.94
1309-11 Ferry 2,108.11 10,693,320.38
1309-11 Ferry 26.13 11,014,437.71
1309-11 Ferry 2,163.00 10,880,751.39


For ROCHEBLAVE, I'd like BALANCE to show 10,373,833.47 rather than the sum
of those two numbers ($20,746,241.94), as it ends up below.

Data
Name Sum of Debit Sum of Credit Sum of Balance
1301 ROCHEBLAVE $1,425.00 $20,746,241.94
1302 Ferry Place $1,510.00 $30,136,384.11
1304-06 Ferry $800.00 $49,801,768.46
1308-10 Ferry $500.00 $59,816,509.40


Any ideas?

 

[smartin]

I have a question about pivot tables--

I have a dataset in Excel with the columns NAME, DEBIT, CREDIT, and BALANCE.
The info was originally drawn from a QuickBooks database. Debit and credit
are from individual entries, and I have them Summed. However, Balance is a
"running balance" field in QBooks, but, when I put it into a pivot table, I
cannot find a way to get ti to show only the LAST entry for a given grouping.
For instance, this is a sample of the data before converting it to a ptable:

Name Debit Credit Balance
1301 ROCHEBLAVE 0.00 10,372,408.47
1301 ROCHEBLAVE 1,425.00 10,373,833.47
1302 Ferry Place 1,510.00 9,390,142.17
1304-06 Ferry 500.00 10,015,240.94
1304-06 Ferry 300.00 9,650,143.41
1308-10 Ferry 500.00 10,014,740.94
1309-11 Ferry 2,108.11 10,693,320.38
1309-11 Ferry 26.13 11,014,437.71
1309-11 Ferry 2,163.00 10,880,751.39


For ROCHEBLAVE, I'd like BALANCE to show 10,373,833.47 rather than the sum
of those two numbers ($20,746,241.94), as it ends up below.

Data
Name Sum of Debit Sum of Credit Sum of Balance
1301 ROCHEBLAVE $1,425.00 $20,746,241.94
1302 Ferry Place $1,510.00 $30,136,384.11
1304-06 Ferry $800.00 $49,801,768.46
1308-10 Ferry $500.00 $59,816,509.40


Any ideas?

One way might be to compute the "last" value on your own:

Assume your data in A2x. Add a helper formula in E2 like
=IF(A2=A3,0,D2)
Fill down, and include column E in your pivot table as Sum Of.