| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pivot Table categories
If I have a datalist like the following, is it possible to produce a
pivot table with a single row for each unique combination of values in column A and B? Basically, I want to see the BALANCE once for each unique combination of CODE! and CODE2. In the datalist, VAL1 is adding to the total; VAL2 is removing from the total, and BALANCE (for the first data row) is calculated with the formula: =SUMIFS(E:E,A:A,A4,B:B,B4)-SUMIFS(G:G,A:A,A4,B:B,B4) A B C D E F G H ---------------------------------------------------------------------------------------------------------------------------------------------------------------- CODE1 CODE2 CODE3 DATE1 VAL1 DATE2 VAL2 BALANCE ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 55478 34001 21987 14-Aug-2009 8 2 55478 34001 23-Sep-2009 4 2 55478 34001 31-Dec-2009 2 2 62323 34001 22387 9-Oct-2009 7 7 62323 20002 21748 9-Oct-2009 14 14 59400 43001 19878 14-Oct-2009 9 0 59400 43001 18-Nov-2009 9 0 .... Any assistance would be greatly appreciated. Thanks, Terry |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivot Table categories
On Aug 17, 11:41*am, Alder wrote:
If I have a datalist like the following, is it possible to produce a pivot table with a single row for each unique combination of values in column A and B? *Basically, I want to see the BALANCE once for each unique combination of CODE! and CODE2. *In the datalist, VAL1 is adding to the total; VAL2 is removing from the total, and BALANCE (for the first data row) is calculated with the formula: =SUMIFS(E:E,A:A,A4,B:B,B4)-SUMIFS(G:G,A:A,A4,B:B,B4) A * * * B * * * C * * * D * * * * * * * E * * * F * * * * * * * G * * * H ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- CODE1 * CODE2 * CODE3 * DATE1 * * * * * VAL1 * *DATE2 * * * * * VAL2 * *BALANCE ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- 55478 * 34001 * 21987 * 14-Aug-2009 * * 8 * * * * * * * * * * * * * * * 2 55478 * 34001 * * * * * * * * * * * * * * * * * 23-Sep-2009 * * 4 * * * 2 55478 * 34001 * * * * * * * * * * * * * * * * * 31-Dec-2009 * * 2 * * * 2 62323 * 34001 * 22387 * 9-Oct-2009 * * *7 * * * * * * * * * * * * * * * 7 62323 * 20002 * 21748 * 9-Oct-2009 * * *14 * * * * * * * * * * * * * * *14 59400 * 43001 * 19878 * 14-Oct-2009 * * 9 * * * * * * * * * * * * * * * 0 59400 * 43001 * * * * * * * * * * * * * * * * * 18-Nov-2009 * * 9 * * * 0 ... Any assistance would be greatly appreciated. Thanks, Terry Sorry, the tabs I inserted to space the data were removed when I posted. Here it is with spaces instead: A B C D E F G H ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- CODE1 CODE2 CODE3 DATE1 VAL1 DATE2 VAL2 BALANCE ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- 55478 34001 21987 14-Aug-2009 8 2 55478 34001 23- Sep-2009 4 2 55478 34001 31- Dec-2009 2 2 62323 34001 22387 9-Oct-2009 7 7 62323 20002 21748 9-Oct-2009 14 14 59400 43001 19878 14-Oct-2009 9 0 59400 43001 18- Nov-2009 9 0 Terry |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivot Table categories
On Aug 17, 11:46*am, Alder wrote:
On Aug 17, 11:41*am, Alder wrote: If I have a datalist like the following, is it possible to produce a pivot table with a single row for each unique combination of values in column A and B? *Basically, I want to see the BALANCE once for each unique combination of CODE! and CODE2. *In the datalist, VAL1 is adding to the total; VAL2 is removing from the total, and BALANCE (for the first data row) is calculated with the formula: =SUMIFS(E:E,A:A,A4,B:B,B4)-SUMIFS(G:G,A:A,A4,B:B,B4) A * * * B * * * C * * * D * * * * * * * E * * * F * * * * * * * G * * * H ---------------------------------------------------------------------------**--------------------------------------------------------------------------*-*---------- CODE1 * CODE2 * CODE3 * DATE1 * * * * * VAL1 * *DATE2 * * * * * VAL2 * *BALANCE ---------------------------------------------------------------------------**--------------------------------------------------------------------------*-*---------- 55478 * 34001 * 21987 * 14-Aug-2009 * * 8 * * * * * * * * * * * * * * * 2 55478 * 34001 * * * * * * * * * * * * * * * * * 23-Sep-2009 * * 4 * * * 2 55478 * 34001 * * * * * * * * * * * * * * * * * 31-Dec-2009 * * 2 * * * 2 62323 * 34001 * 22387 * 9-Oct-2009 * * *7 * * * * * * * * * * * * * * * 7 62323 * 20002 * 21748 * 9-Oct-2009 * * *14 * * * * * * * * * * * * * * *14 59400 * 43001 * 19878 * 14-Oct-2009 * * 9 * * * * * * * * * * * * * * * 0 59400 * 43001 * * * * * * * * * * * * * * * * * 18-Nov-2009 * * 9 * * * 0 ... Any assistance would be greatly appreciated. Thanks, Terry Sorry, the tabs I inserted to space the data were removed when I posted. *Here it is with spaces instead: A * * * B * * * C * * * D * * * * * * * E * * * F G * * * H ---------------------------------------------------------------------------**--------------------------------------------------------------------------*-*---------- CODE1 * CODE2 * CODE3 * DATE1 * * * * * VAL1 * *DATE2 VAL2 * *BALANCE ---------------------------------------------------------------------------**--------------------------------------------------------------------------*-*---------- 55478 * * 34001 * * 21987 * * 14-Aug-2009 8 * * * * * * * * * * * * * * * * * * * * *2 55478 * * 34001 * * * * * * * * * * * * * * * * * * * * * * * * * *23- Sep-2009 * * 4 * * * * 2 55478 * * 34001 * * * * * * * * * * * * * * * * * * * * * * * * * *31- Dec-2009 * * 2 * * * * 2 62323 * * 34001 * * 22387 * * * 9-Oct-2009 7 * * * * * * * * * * * * * * * * * * * * 7 62323 * * 20002 * * 21748 * * * 9-Oct-2009 14 * * * * * * * * * * * * * * * * * * * 14 59400 * * 43001 * * 19878 * * 14-Oct-2009 9 * * * * * * * * * * * * * * * * * * * * 0 59400 * * 43001 * * * * * * * * * * * * * * * * * * * * * * * * * *18- Nov-2009 * * 9 * * * * 0 Terry- Hide quoted text - - Show quoted text - Hmm. I'll have to find the HOWTO for posting fixed-width data. Sorry about this mess. Thanks, Terry |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivot Table categories
Alder wrote:
If I have a datalist like the following, is it possible to produce a pivot table with a single row for each unique combination of values in column A and B? Basically, I want to see the BALANCE once for each unique combination of CODE! and CODE2. In the datalist, VAL1 is adding to the total; VAL2 is removing from the total, and BALANCE (for the first data row) is calculated with the formula: =SUMIFS(E:E,A:A,A4,B:B,B4)-SUMIFS(G:G,A:A,A4,B:B,B4) A B C D E F G H ---------------------------------------------------------------------------------------------------------------------------------------------------------------- CODE1 CODE2 CODE3 DATE1 VAL1 DATE2 VAL2 BALANCE ---------------------------------------------------------------------------------------------------------------------------------------------------------------- 55478 34001 21987 14-Aug-2009 8 2 55478 34001 23-Sep-2009 4 2 55478 34001 31-Dec-2009 2 2 62323 34001 22387 9-Oct-2009 7 7 62323 20002 21748 9-Oct-2009 14 14 59400 43001 19878 14-Oct-2009 9 0 59400 43001 18-Nov-2009 9 0 ... Any assistance would be greatly appreciated. Thanks, Terry Add a column in your data that combines CODE1 and CODE2. Something like this: =A2&" "&B2 Put that field in the ROW FIELDS of the PivotTable. In the PivotTable, add a calculated field that is =VAL1-VAL2 and put that in your DATA ITEMS. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Pivot Table categories
On Aug 17, 12:18*pm, Glenn wrote:
Alder wrote: If I have a datalist like the following, is it possible to produce a pivot table with a single row for each unique combination of values in column A and B? *Basically, I want to see the BALANCE once for each unique combination of CODE! and CODE2. *In the datalist, VAL1 is adding to the total; VAL2 is removing from the total, and BALANCE (for the first data row) is calculated with the formula: =SUMIFS(E:E,A:A,A4,B:B,B4)-SUMIFS(G:G,A:A,A4,B:B,B4) A *B * * * C * * * D * * * * * * * E * * * F * * * * * * * G * * * H ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- CODE1 * * *CODE2 * CODE3 * DATE1 * * * * * VAL1 * *DATE2 * * * * * VAL2 * *BALANCE ---------------------------------------------------------------------------*---------------------------------------------------------------------------*---------- 55478 * * *34001 * 21987 * 14-Aug-2009 * * 8 * * * * * * * * * * * * * * * 2 55478 * * *34001 * * * * * * * * * * * * * * * * * 23-Sep-2009 * * 4 * * * 2 55478 * * *34001 * * * * * * * * * * * * * * * * * 31-Dec-2009 * * 2 * * * 2 62323 * * *34001 * 22387 * 9-Oct-2009 * * *7 * * * * * * * * * * * * * * * 7 62323 * * *20002 * 21748 * 9-Oct-2009 * * *14 * * * * * * * * * * * * * * *14 59400 * * *43001 * 19878 * 14-Oct-2009 * * 9 * * * * * * * * * * * * * * * 0 59400 * * *43001 * * * * * * * * * * * * * * * * * 18-Nov-2009 * * 9 * * * 0 ... Any assistance would be greatly appreciated. Thanks, Terry Add a column in your data that combines CODE1 and CODE2. *Something like this: =A2&" "&B2 Put that field in the ROW FIELDS of the PivotTable. In the PivotTable, add a calculated field that is =VAL1-VAL2 and put that in your DATA ITEMS.- Hide quoted text - - Show quoted text - Exactly what I needed. Thanks, Glenn. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| All categories to show in pivot tables for use as dataframe? | Excel Worksheet Functions | |||
| How to print different charts from multiple categories of pivot ch | Excel Discussion (Misc queries) | |||
| How do i sort and count data into categories using a pivot table? | Excel Discussion (Misc queries) | |||
| Are Pivot Charts only useful with limited number of categories(x-axis)? | Charts and Charting in Excel | |||
| How I can add categories in a pivot table? | Excel Worksheet Functions | |||
Linear Mode
