disagreeing pivot tables

[miconian]

I have two pivot tables that seem to produce different results (in the summed numerical data), even though, as far as I can tell, they are identical in every other way.

One of them has been in the workbook for a very long time. That one is the incorrect one.

The other one, which I created a few minutes ago, is correct. I say "correct" in that it matches the totals that I get manually adding up the raw data.

I calculated the difference between the two for a particular item, and searched the raw data for that difference, but could not find it.

The number in the incorrect data is *higher* than in the correct data.

I realize that I should delete the old pivot table. But it's important that I find out the cause of the problem.

I don't think I can reproduce this problem (how could I?), but I am happy to look at any settings for the incorrect table that anyone suggests.

Anyone encountered something like this?

I'm in Excel 2007, in an .xlsx file, on Windows XP. The workbook contains a lot of vlookups, but is otherwise not very complicated.

 

[miconian]

I didn't use a formula to create the table. I just selected all the data, and clicked Insert -> Table. Then I named the table.

The workbook is loaded with confidential information, so there is no way I could show it to anyone, sorry.

 

[miconian]

Here's an experiment I conducted re the data source:

- I copied the correct pivot table.
- I changed the new pivot table's data source to match that of the incorrect pivot table.
- I refreshed the new pivot table.

Result: The new pivot table still shows the correct data, same as the other correct pivot table. The data source is not the problem.

 

[jasonb75]

Here's an experiment I conducted re the data source:

- I copied the correct pivot table.
- I changed the new pivot table's data source to match that of the incorrect pivot table.
- I refreshed the new pivot table.

Result: The new pivot table still shows the correct data, same as the other correct pivot table. The data source is not the problem.

What about reversing that procedure, changing the data source of the incorrect table?

 

[miconian]

Good idea, jason. I reversed the experiment, and... it made no difference. The incorrect table is still incorrect, even with the correct data source.

 

[jasonb75]

I was expecting that but wanted to confirm.

Not having the sheet in front of me, I can only throw you suggestions of what might be.

Searching the web comes up with things such as http://support.microsoft.com/kb/211470 but I would have thought that if this was the case then both would show the same results.

Other than going through the options for both tables comparing every setting, I'm running out of ideas.

 

[Tardis]

Hi, Trying to reproduce what you describe. See what happens.

 

[miconian]

I figured it out. One of the fields was different. There are two fields with a very similar purpose and spelling, and I was using one in one table, and the other in the other table.

Argh, I am so embarrassed. I almost didn't want to come back to the board and tell you guys what happened. I had to have two of my co-workers come to my desk and watch as I went through the settings, and one of them caught it.

So... thank you so much. Sorry to waste everyone's time.

 

[jasonb75]

I figured it out. One of the fields was different. There are two fields with a very similar purpose and spelling, and I was using one in one table, and the other in the other table.

Argh, I am so embarrassed. I almost didn't want to come back to the board and tell you guys what happened. I had to have two of my co-workers come to my desk and watch as I went through the settings, and one of them caught it.

So... thank you so much. Sorry to waste everyone's time.

Haha, easily done, sometimes a fresh pair of eyes is the only way to find the problem.