Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
pivot tables - show one entry from a group
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? |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
pivot tables - show one entry from a group
EricKei wrote:
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 A2:Dx. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group Error in Pivot Tables | Excel Discussion (Misc queries) | |||
Pivot tables - Group Label on each line | Excel Discussion (Misc queries) | |||
Why aren't dates always available to group in pivot tables? | Charts and Charting in Excel | |||
Group function while using Pivot tables | Excel Worksheet Functions | |||
Pivot Tables: Unable to Group and Show Detail | Excel Discussion (Misc queries) |