Pivot table consolidation

[Ariella]

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??

 

[Jim Thomlinson]

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