• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivots data labels -- Abbreviations vs Full Name

Dr. Demento

Dr. Demento

Member
First of all, I'm not even sure how to properly phrase the question succinctly, so if this has already been answered, my apologies - point the way and I'll go back to my corner and color.

I have a number of rather large datasets that I use Pivot Tables to extract summary statistics. Some of the raw data in the data fields are rather long and so I've used abbreviations (i.e., Name = JJJS instead of "John Jacob Jingleheimer Schmidt"). This keeps the raw data managable.

However, when I construct the Pivot (which I'm often doing for others who do not know what the abbreviations stand for), I'd like the original value to read "John Jacob Jingleheimer Schmidt." Is there any way to keep the raw data using the abbreviation but have the pivot table use the full data value? This would be akin to using an Access lookup table.

Thanks much.
 
Hi Dr.Demento..

Welcome to this forum!

This can be done but not be in the same PT.

You will continue to keep the existing PT. Plus you will have to create your a separate report with expected name with columns like abv and full name, and other necessary columns that you have in PT.

Once the layout of the report is done, use GetPivotTable() as formula to fetch info from PT to this report.

Hide the column abv.

Regards,
Prasad DN
PS: AFAIK, If you looking to edit the name in the PT, then it is not possible.
 
Prasad,

Thanks much! I assume that since the data is being "transferred" by GetPivotData from the PT to the report, none of the usual amenities of a PT are available? By that, I mean able to re-arrange row/col values using drag/drop, expanding/collapsing, etc?

Not ideal, but I hear you.

Thanks much!!
 
Hi ,

I don't know whether I have understood you clearly ; suppose in your data range you could have an additional column where you have a formula to lookup the abbreviations in a lookup table , and insert the full name corresponding to the abbreviations , so that your data range has both the abbreviations column and the full names column.

In your pivot table , all you need to do is select the column you want.

Narayan
 
You must log in or register to reply here.
Back
Top