Pivot Table - Date issue

[SiriusProjects]

Hi All,

I'm sure similar threads have been posted regarding my query, so apologies if this has already been answered.

I'm having a real issue getting data in a pivot table to sort correctly.

We have a spreadsheet containing a log of any praise/complaints received and I am using a pivot table to format the data and to produce a pivot chart.

The columns in the pivot table consist of the following.

January 2019 1
Praise 1
February 2019 2
Complaint 1
Praise 1
March 2019 2
Complaint 1
Praise 1

The problem occurred when I added a new record for May 2019. When I refreshed the Pivot Table I was expecting to see the May 2019 entry at the bottom underneath march, but instead it was inserted at the top.

I found some posts online that mentioned that it may be because it's formatted as text, which it is, but wouldn't May still appear under March in alphabetical order if that's the case?

Any help would be appreciated.

Thanks

[Roel Jongman]

but wouldn't May still appear under March in alphabetical order if that's the case?

yes, unless I have missed the memo the alphabet has not changed recently

The issue could be as simple as that there is a space before the M of May, spaces are sorted before A.
it can also be another (special) character in front of eighter may or the other values. Even it could be that some dates are true dates and others are text dates that will also affect sorting.

also there if may 2019 is a true date then it is a number and numbers are also sorted before A / text.

without seeing the file it is not possible to know the exact cause but "strange sorting" is due to inconsistencies in the datatable..

[SiriusProjects]

Thanks for the reply Roel.

I think I may have solved the problem.

I have created a VBA front end which includes a reporting form. When the user selects a specific year to report on the relevant data is copied to a reporting sheet and the pivot tables are refreshed programmatically based on the new data. However, old entries within the pivot table were not being removed and I think this was causing the sorting inconsistency.

I accessed the pivot table options and ensured that the "Number of items to retain per field" dropdown was set to none (it was set to automatic). This seemed to do the trick as the pivot table now sorts correctly each time it is refreshed.