A Pivot table issue

  • Thread starter Thread starter Yakimoto
  • Start date Start date
Y

Yakimoto

Hi everybody!

Is it possible to create a pivot table, but to use only a part of a field as
a row filed, not an entire field.
I have in the column 'Parts' thge following data
Parts Numbers
---------
Vehicle-Doors 1
Vehicle-Wheels 2

and I woulf like to create a pivot, but somehow to have Left(part,6), so I
will have grouping by Vehicle only.
I can manage this adding additoonal column to the sheet, but I think there
should be a way using some formula or so.

Thanks in advance

../Yakimo
 
Hi,
You'll need to create a new column and place formula
like you have written, but use '=left(A1,Find("-",A1)-1)'.
You can then use this new column in your pivot table. You
need to give the column a name as well, like Part Group or
something, your choice.

regards
KM
 
Thanks, Kevin.
With a new column it is clear how to do.
I was just wondering if there is a way without adding additional columns.
Does anyone now something?

Cheers,
Yakimoto
 
Hello Yakimoto

The simplest solution is to create a new field in your
data source. Insert a column to the right of the vechile-
number column. Then use the Text to Columns from the
Excel DATA drop down menu. With that command you can
parse the numbers into the new field. Once that is
finished you can form the pivot table you want.

This breakes the original field into two fields. If you
need to keep the original field intact then you could use
the Mid() function to copy only the desired portion of the
original field into the new field.

Mike
 
Thanks Mike,
Is there any other solution without adding a new column? Is it posible to do
that using pivot table formulas (calculated fields, calculated items or
whatever) ?
 
Insert a new column A and put in a formula like


=left(B2,6)

Put in an appropriate label/header in A1. Drag fill A2 down the column.

Now create you pivot table.
 
Back
Top