Pivot Table question

[JulieD]

Hi All

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

Cheers
JulieD

 

[Earl Kiosterud]

JulieD,

I don't think there's a way to group on part of a column. You'll probably
find your table more useful in general if you split the car type from the
color anyway.

Your example table will appear differently depending on the font used in the
user's newsreader. It's a good idea to use a non-proportional font like
Courier.

 

[Toby Erkson]

As a car enthusiast, pivot table user, and Excel reporting dude I agree with
Earl, you need to separate manufacturer from color. This would also allow you
to include other important fields like "Model" and "Year". Don't want the
uninformed thinking a 2002 is a year ;-)

 

[JulieD]

Hi guys

thanks for the responses .. .my data isn't about cars i just thought that
was an easier thing to type to illustrate the point than the 50 character
items i'm dealing with.

currently the data is split into different columns but as i've now got about
50 columns of data i'm trying to simplify things a bit and was hoping that i
could use some of the text functions in the pivot tables when i need it
rather than in the underlying data.

Cheers
JulieD

As a car enthusiast, pivot table user, and Excel reporting dude I agree with
Earl, you need to separate manufacturer from color. This would also allow you
to include other important fields like "Model" and "Year". Don't want the
uninformed thinking a 2002 is a year ;-)

 

[Toby Erkson]

Okay, I understand. I would recommend posting your question to the
"microsoft.public.excel.charting" newsgroup. Debra Dalgleish is in there and
she's a great source for pivot table info.