Pivot table consolidation
 

i have a very large data dump from which i've created a pivot table with
multiple column headers and corresponding sales. I'd like to have a pivot
which gives me an aggregate of the first column's items within the first
column of the pivot table. For example, see data below:

Cat1 Cat2 Cat3 Sales$
A1 B1 C1 1
C2 2
B2 C1 3
C2 4
A2 B1 C1 5
C2 6
B2 C1 7
C2 8
A1+A2 B1 C1 6
C2 8
B2 C1 10
C2 12

In my question, I'm seeking to create A1+A2 in column 1 and the
corresponding columns which provide the sales as a sum of the other columns
entries (as shown). I want to do this without changing the data source. I
know I can move the first column to the 3rd and group A1 & A2, but am looking
for an alternative in the structure of the data above. Impossible mission??

Pivot table consolidation
 

So what you have right now is
Cat1 Cat2 Cat3 Total
a1 b1 c1 1
c2 2
b2 c1 3
c2 4
a2 b1 c1 5
c2 6
b2 c1 7
c2 8

How about just pivoting cat 1 up top...
Cat2 Cat3 a1 a2 Grand Total
b1 c1 1 5 6
c2 2 6 8
b2 c1 3 7 10
c2 4 8 12


--
HTH...

Jim Thomlinson


"Ariella" wrote:

i have a very large data dump from which i've created a pivot table with
multiple column headers and corresponding sales. I'd like to have a pivot
which gives me an aggregate of the first column's items within the first
column of the pivot table. For example, see data below:

Cat1 Cat2 Cat3 Sales$
A1 B1 C1 1
C2 2
B2 C1 3
C2 4
A2 B1 C1 5
C2 6
B2 C1 7
C2 8
A1+A2 B1 C1 6
C2 8
B2 C1 10
C2 12

In my question, I'm seeking to create A1+A2 in column 1 and the
corresponding columns which provide the sales as a sum of the other columns
entries (as shown). I want to do this without changing the data source. I
know I can move the first column to the 3rd and group A1 & A2, but am looking
for an alternative in the structure of the data above. Impossible mission??