Replacing data in Pivot Table causes Field name change

[Guest]

Good morning,

I have a pivot table report that uses about 1200 rows of data on a
neighboring page. Today I produced new monthly data and pasted it on top of
the old data. When I do this the report's left most column field names all
change from FieldName to FieldName2. When I look in the drop down box for
that field it seems to have duplicated the fields when I pasted the new data.
I'm able to work around it by changing the displayed lables from FieldName2
to FieldName"space""space". I can't change to just FieldName because that is
already in use. My fear is that next month when I do this again it will
again dup the field name and I'll have to put in "space""space""space" etc.

Any idea why it does this? I've tried:

* deleting the data then pasting the new data
* deleting the rows then pasting the new data
* leaving the column headings in place and pasting only data
* pasting everything including column headings

The new data is slightly larger than the old. I have 2 versions of the
report. One pulls from a static page. For the new data after I paste I have
to go into the wizard and go "back" so as to increase the range of the data.
My other version of the report uses a dynamically calculated Named Range, so
all I have to do is paste and I'm good.

Thanks for any ideas.

Russ

 

[CloudDoctor]

Sounds like you still have the original 'FieldName' in the data that
the report relates to - can you make sure it isn't included and only
the data for the new month is? ie when you 'go back' to change the
range the report points at make sure the range only includes the new
and not the old?

 

[Guest]

CloudDoctor,
Can't do. My original data range is A1:L209. The new data is A1:L1339.
The new data is larger than the previous and is totally new data so I can
just tack in on the end. I'm stumped.

 

[Guest]

I see from contextures.com the below hint. I guess that is my problem.
Excel simply adds the new data on top of the old data cache. Since the
column headings are the same as the old column headings it appends a "2" to
the end of it to differentiate. To me that makes the pivot table about
worthless. Is my understanding correct here? Also, I tried the instructions
for the manual clear of old items and it didn't work. - Russ

Old Items Remain in Pivot Field Dropdowns

The data in the pivot table source may change, and items that were
previously in the source are no longer included. For example, some sales reps
may leave the company, and the names of their replacements appear in the
source table.

Even after you refresh the pivot table, the names of the old sales reps will
appear, along with the new names. In the list at right, Cartier has replace
Gill, but Gill still appears in the list.




Manually Clear Old Items

To manually clear the old items from the list:

Drag the pivot field out of the pivot table.
On the Pivot toolbar, click the Refresh button
Drag the pivot field back to the pivot table

******************

 

[Debra Dalgleish]

Maybe you're getting some false groupings.
Select one of Fields that ends with a 2

 

[Guest]

Hi Debra,

That's not it. I now know what the issue is, but still not how to correct.
Aditionally, I should have done a better job of more accurately describing
the problem as Value duplication, not Field duplication...

Originally this PT report Field had values of ValueAA, ValueBB, ValueCC.
When she saw the report the user didn't like the value labels and asked if
they could be changed. I found out that all one had to do was go into the PT
Report and edit the value and PT changed the values to ValueXX, ValueYY,
ValueZZ across the board. Cool! This month the lady who produces in
Business Objects the data for me to use as input also modified the Field to
use ValueXX, YY, ZZ. Thus the PT somehow sees these new values coming in as
conflicts??? with the modified values that I overlayed and it screws things
up. (Technical term.)

Knowing this, it would seem plausible that all I would have to do is what I
just tried:

* remove the dup field column from the report
* add the new data to the data page
* hit Refresh (without the field on the report)
* add the field back on to the report

In this way I would think it would clear out the old "overlay" values that I
had input before. It does not. It still keeps them internally somewhere and
chokes on the new data. Maybe with my new more accurate description what I
need to do to fix is more obvious??? He hopes....

Thanks

 

[Roger Govier]

Hi

Did you try Debra's code for removing old items?
This invariably works for me when I have old items in the list.
http://www.contextures.com/xlPivot04.html

 

[Guest]

Roger,

That did the trick. I was trying to avoid using code since I'm a contractor
and will leave here some day leaving this to folks who won't attempt any
code. However, since it seems this is more like a one time clean up it
shouldn't be a problem. Thanks for the advise.

Thank you Debra for the corrective code.

Regards

 

[shanedevenshire]

Roger,

That did the trick. I was trying to avoid using code since I'm a contractor
and will leave here some day leaving this to folks who won't attempt any
code. However, since it seems this is more like a one time clean up it
shouldn't be a problem. Thanks for the advise.

Thank you Debra for the corrective code.

Regards






- Show quoted text -

Hi,

Using code to clear the unwanted items is not necessary if you are
using Excel 2002 or later. Simply drag the offending field of the
pivot table, click Refresh, and drag the field back on to the table.

Cheers,
Shane Devenshire
Microsoft Excel MVP

 

[Roger Govier]

Hi Shane

Using code to clear the unwanted items is not necessary if you are
using Excel 2002 or later. Simply drag the offending field of the
pivot table, click Refresh, and drag the field back on to the table.

I am aware of this, and that is what I try first.
There are some occasions where this does not work, however, as the OP
had already tried and noted it didn't work.

The code solution always does achieve the desired outcome.

 

[Debra Dalgleish]

If you run the code for 2002:

http://www.contextures.com/xlPivot04.html#Clear2002

it sets the MissingItemLimit property to zero, so the problem shouldn't
recur. In Excel 2002 and 2003, that setting is only accessible via
programming.