Pivot Table categories

A

Alder

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
 
A

Alder

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
Click to expand...

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
 
A

Alder

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 -
Click to expand...

Hmm. I'll have to find the HOWTO for posting fixed-width data. Sorry
about this mess.
Thanks,

Terry
 
G

Glenn

Alder said:
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
Click to expand...


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.
 
A

Alder

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 -
Click to expand...

Exactly what I needed. Thanks, Glenn.
 
Top