Need help with simple REPLACE / SUBSTITUTE formula for 2 pivot tables

Hi,

I've got two pivot tables on one worksheet. Each has data with codes (ex: CAM for campaign, CLI for client, OTH for other, etc). I want to be able to stick in formulas that will replace the codes with the full text in both pivot tables. The codes are in column A in the first pivot, and column I in the second. I can't seem to get anything working

are you able to attach a sample workbook?

Hi Maym, attached

can you not include the descriptions (i.e. full text) in both of your spreadsheets (Inquiries and comm) and then pivot off that?

technically I could run a formula on those data sets (generated by our system) every time we drop them in the worksheets, but I was hoping to have a set formula on the main summary sheet so it automatically did it. Not possible?

A calculated field would help but only if you have values (text would not work so you couldn't do something like =if(field1="code",.."

The only way I know is to add the if function to the data sets. You could write a macro so you don't need to run the formula every time?

Thanks. I'm not good with macros so won't be able to do that :-/

well, status and status description already appear to be in the comm sheet, is this how the data comes in, or did you need to add this manually? On Inquiries sheet, you can also add lead interest description, or did you add lead interest manually?

You can run a macro when you drop the data into your sheets, if I have the right info I can help you with this.

Hi maym, all the data in the Comm and Inquiries tabs are how our system spits them out - not able to add more description columns unfortunately - got lucky with the status and lead interest description columns.

so when the system spits them out, are you not able to add columns when you put them into your summary workbook? Or are you not allowed to add any more columns period? If you can, upon import you can run a macro but just checking this first.

we can add columns, but ideally trying to make it as easy as possible and not have to mess around adding multiple things each time

ok. Pretty sure it is difficult to manipulate the field in the pivot table (particularly a text field) so macro is the only way I can think of.

You could set it up so that you run it each time which wouldn't be difficult and would give you the required result. But sounds like you a looking for solution to my first point.

we could run it each time

Could you show the result you want? Mock up manually if necessary.

Hi Sandy, example attached (desired result in yellow)

Thanks
I think {} it can be done via PowerPivot related Table
but ususally Ex2010 need free add-in from MS like PowerPivot and PowerQuery

Maybe VBA will be a good solution but I am away from VBA

and I thought this would be an easy one

It is but many clicks

so, what about mentioned add-ins?

or you prefer VBA without add-ins

a macro would do it, I am just pushed for time right now. Will be able to get back to you later on it

I c vba is in progress
so have a nice day and good luck

@shukla.ankur281190

post#3 & post#15

try this attached also (click the 'run update' button on the summary sheet).

Just for fun

but PowerPivot add-in is required (PowerQuery add-in is very usefull also)

Seem to have gotten in over my head with the macros and vba- will need to do some studying before I can try again to make these work.

Thank you everyone!

Hi Megs, I made some changes. Please see if the attached works.

BTW, I would just use xlnitwit's code, it works directly off the pivot table and eliminates the additional work I have done in mine.

version attached with xlnitwit's code. I have added a button for ease of use. You can decide which one you want to use.

Oh wow - it worked! xlnitwit :-)

Thank you all for your work and suggestions - very much appreciated

maym - awesome! Thank you so much

glad to help

If that takes care of your original question, and to say thanks, please click on 'Add Reputation' (bottom left corner of the post of the person(s) who helped you), then select Thread Tools from the menu (top right corner of your thread) and mark this thread as SOLVED.