Pivot Table Question

[JulieD]

Hi All

I originally posted this in microsoft.pulic.excel but was advised to post
here as well - so please don't yell at me about posting to multiple groups


***
is there a way in a pivot table to only use (& therefore group on ) part of
a value name ... e.g.
if i have
Car Number
BMW - Blue 100
BMW - Red 20
VOLVO - Blue 50
VOLVO - Red 10

and i want a pivot table to look like this

Blue Red Total
BMW 100 20 120
VOLVO 50 10 60
150 30 180

is is possible to do this in the pivot table itself rather than in the
underlying data (which i know how to do) - using Excel 2000. The reason is
that i have more complex data than that shown above and to get all the
permutations that are required of the data i'm using about 40 columns of
left & mids & right functions in the workbook. I'm hoping to simplify the
workbook by doing these functions in the multiple pivot tables that are
created from the data.

Cheers
JulieD

 

[Toby Erkson]

I told her to post here cuz I know Deb D. hangs out here Also, her data
below is simply an example of what she wants...she's not really working with
automobile data (bimmer, er I mean, bummer).

 

[Debra Dalgleish]

AFAIK, you'll have to do the parsing in the data source, rather than the
pivot table. Or talk to the people who provide the data, and see if it
can be split into the appropriate fields before it reaches you.

You could manually group the text fields in the pivot table, but I'm
sure that would be more work than maintaining the additional formulas in
the source table.

And please don't multipost! ;-)

 

[JulieD]

Hi Debra

thanks for the response ... i'll go write a wishlist message for the next
version

and i promise never to multipost again.

Have a good day.

Cheers
JulieD