Duplicate/incorrect items showing in Pivot table filter

[Jyae31]

Afternoon Ladies/Gents.

I've come across something this afternoon that I've never seen before and I don't quite know the cause.

To summarise, there is a pivot table looking at a huge chunk of data (to big for excel to be efficient with but that's another story), and every so often once additional data is being added to the source file, The filter on the pivot table begins to tell lies.

There are 150 unique business units collecting data. Out of those business units, when I filter on 4 of them (either scroll down the list or type the digits into the filter) the results of the pivot are giving the data from a different business unit.

I've checked the data on the source file. Nothing out of the ordinary, formats seem good etc. I've only just started to handle this data and it seems like its been common practice to "Delete the pivot and re-build it" to fix it and well, thats not really ideal for me.

Has anyone come across this before? Where a pivot is showing data, different to that which you have filtered for? One of the business units is showing a duplicate in the filter: Image Here

Any light shed on this would be much appreciated.

Thanks in advance,

James.

 

[ChrisM]

Yes, I've had this happen to me, and it is a super scary bug, you'll never know your data is bad. It happens to me when I use VBA to set pivot items. If the pivot item does not exist in the drop down list VBA will still set the item, but it will overwrite whatever is currently selected. I've also had the issue with the same item appearing twice, one is equal to current data, and the other is equal to a completely different item. Same cause, using VBA gets things out of sync.

The only fix I found is to rebuild the pivot table, and in the future before setting fields with VBA you always have to test whether they exist or not. I use an OnError statement for this.

 

[RoryA]

You would see that duplication if one of the numbers is stored as text. I've never seen the wrong data when filtering - I have seen a couple of mentions of it ever, but couldn't verify them.

 

[Jyae31]

Yes, I've had this happen to me, and it is a super scary bug, you'll never know your data is bad. It happens to me when I use VBA to set pivot items. If the pivot item does not exist in the drop down list VBA will still set the item, but it will overwrite whatever is currently selected. I've also had the issue with the same item appearing twice, one is equal to current data, and the other is equal to a completely different item. Same cause, using VBA gets things out of sync.

The only fix I found is to rebuild the pivot table, and in the future before setting fields with VBA you always have to test whether they exist or not. I use an OnError statement for this.

Chris – Glad it’s not just me cracking up then. The rebuilding of the pivot seems to of been the process the guys used prior to me looking at it. I'll code in something to allow them to rebuild said pivot as and when required for ease but it would be nice to know of something that could be put in place to determine the integrity of the data.

You would see that duplication if one of the numbers is stored as text. I've never seen the wrong data when filtering - I have seen a couple of mentions of it ever, but couldn't verify them.

Rory – I’ve just gone in this morning after reading your reply just to ensure I’ve not missed within the reams of data and unfortunately, I haven’t.

It’s a worrying thing because these pivots are driving the selected data onto the company’s P&L and well… we don’t want to be reporting inaccurately. As I mentioned in my OP, it’s only affecting 5 out of 150 company business units and a re-build of the pivot table rectifies the issue, so I don’t believe it’s down to cell formatting as the re-build would suffer from the same issue?

Cheers for the quick replies.

 

[RoryA]

Do you have your pivot table set to retain missing items?

 

[Jyae31]

Its set to:

Number of items to retain per field: None.

To be honest that's one of the things I had missed originally but had changed it prior to posting here.

 

[RoryA]

Have you had the issue arise since changing that and refreshing the data?

 

[Jyae31]

Yea it stays the same. I've been as picky as to change the setting to 'none', save and re-open. Refresh the pivot and the problem persists.

 

[ChrisM]

Are you manipulating the pivot tables with VBA like I cited in my post?

 

[bidya271]

I am facing the same issue. Tried removing filters as well from the base data and tried rebuilding the pivots after 1 or 2 runs the problem persist. Please help if you guys have come across any solution.