Excel Pivot Table Appends a '2' to the field data items

[SunshineX]

My pivot tables work great but sometimes when I update them the field data entries are appended with a '2'

For instance a list of locations might contain the entry "Houston2" but the original data for that was just "Houston"

After remaking the pivot table and putting the exact same setup for it, it work fine again....for a while..

Why is this happening to me !? HELP!!

 

[xlbo]

You are grouping the field.

If you GROUP 2 items, the new level group is named the same as the previous one but with a 2 at the end.

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Hi,

Dou you have a SECOND OCCURRENCE of a HEADING in your table?

BTW, it's not a good table design that has DATA in HEADINGS. The Heading ought to be something like CITY and the DATA: Houston, San Antonio, Dallas, Paris...

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

No, its not the headings, this is actually just a straight list, named 'Locations' then in the list it says "Houston"...but after messing with it for a while it will say "Houston2", then it never goes back

 

[SkipVought]

"...after messing with it for a while..." what messing did you do JUST BEFORE it appended the '2'?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

I re-sorted the data that the table comes from and then refreshed the table.

The wierd thing is, making the table again, with the same source data, eliminates the 2.

Then later altering the data (I have to add more entries somtimes), it will choose one and 2 it !

 

[SkipVought]

How do you redefine your Source Data Range when you add rows?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

Well, truthfully, It has a huge range selected, up to row 1233, and one of the entries in the pivot table is (blank)

That way when I add data it still usees the same range.

Re-selecting the range does not affect it, I am using the pivot table wizard.

 

[xlbo]

Then later altering the data (I have to add more entries somtimes),

please explain EXACTLY what you do when you "alter the data"

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

I almost never use COLUMN RANGE to define my PT Source Data -- rather a DYNAMIC NAMED RANGE, FYI How can I rename a table as it changes size faq68-1331 using the OFFSET function in Insert/Name/Define.

Please post a sample before and after of your SOURCE DATA that causes this to occur and explain EXACTLY what fields are in PAGE, ROW, COLUMN, DATA.



Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

It needs to be sorted in Alphabetical Order
Also sometimes new entries are added.

Other than that it is just a straight list

 

[SkipVought]

One Column Named Location

Location in ROW area?

What's in DATA AREA?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

It's 'location' in the Row field, then I put Location in the space above that also, the 'Count of' box. That is so the table will smash down and be smaller only using 2 columns, it is not really needed.

Nothing else in any other fields.

 

[SkipVought]

Why does "It needs to be sorted in Alphabetical Order"?

There is actually NO NEED to sourt the Source Data.

How are you sorting? Is your Location Heading getting sorted too?


Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

It needs to be sorted in Alphabetical order because another worksheet reads this data and uses it as a lookup table. However the pivot table just reads this one column (Location) and shows a 'Master List' of Locations.

 

[SkipVought]

Of what use is a lookup table if there are multiple occurrences?

What is the lookup actually doing?

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SunshineX]

It's an extensive multipage workbook that tracks an incoming feed from a SQL server, coverts the data multiple times to produces graphs and charts based on alot of other unrelated junk.

The part that is bugging me about it, is just the simple pivot table is acting up. All I want is to have it look at a column from the report, and tell me a list of every 'Location' that's in that column. The 'count of location' works good, It tells me how many times each location is in the list, but I don't use that data. Some are in the list 100's of times. But then every once in a while it will say "Houston2" where it used to say Houston. I don't know how to make it go back, except to remake the whole table using the same source data.

 

[xlbo]

1: Do you ever alter the data in the pivottable itself?
2: do you do any drag and drop movements to sort the pivottable ?
3: Do you rename any fields or indeed any ITEMS in any fields ?

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

How about just using MS Query using YOUR WORKBOOK as the database or the SQL database, for that matter, and select DISTINCT Location.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}

 

[SkipVought]

I have a feeling that you've made this WAYYYYYYYYYY too complex.

Skip,

_{[red]Be advised:[/red]We know Newton's 3 Laws. But did you hear about the [red]FOURTH???[/red]
Only ONE fig per cookie!}