Ranking and grouping issue

[bimmer5]

As I was working on an Excel pivot sales report based on MS Access records, I came across an Excel conflict between its ‘Top10’ ranking (sorting) functionality and the ‘calculated fields’ functionality that I also need to use in this report in order to find the differences between the sales record fields. It seems I cannot use the both functionalities at the same time, Excel returns an error.
I wonder if the ranking and grouping can be done in MS Access before presenting it to Excel?

Details:
I’ve got multiple requirements for this report:
- To compare the current year sales records vs. the last year sales records (to find sales and sales % differences)
- To rank the customers based on their current year sales records, the best customer ranked #1, etc…
- To group the first (e.g. five) top customers into a ‘Top5’ group and the rest in the second group (.e.g ‘Others’) and subtotal each group.

Attached is a MS Access sample dataset containing the last four years sales records for 10 customers as well as the Excel pivot table report.

Any suggestion is appreciated.
Thanks.

 

[JHB]

Have you got solved your issue?

 

[bimmer5]

No, I haven't resolved it yet.

 

[JHB]

I've made a sample for you in the attached database, open the only form in it, choose fiscal from/to and click the button.

 

[bimmer5]

Hi JHB,
this is great for start, thank you very much for your time and effort.
I was wondering if I can ask you to help me 'fine tune' it?
If so, let me know and I'll follow up.
Thanks again!

 

[JHB]

Ask the question, then we'll see.

 

[bimmer5]

Great, thanks a lot JHB!
My report is a bit more complex; I initially submitted a simplified version of it, to make it easier to understand.
1. My source table will also include the ‘quantity sold’ column. In the report I need to show the quantity columns for both fiscal years along with the ‘differences’ and ‘difference %’ between them (the same as with the sales columns). No need for any sorting or ranking for the quantity, the quantity columns just need to be there. Please see attached file how the report titles will look like in Excel.

2. The final report should be in Excel pivot table; I normally use the Data>From Other Sources>From Microsoft Query>Databases>MS Access>Browse menu to connect to a MS Access query of interest. I was wondering if we can have a query that I can connect to it from Excel, instead of the form or the report you already created? I would need to attach some additional tables to it in MS Access in order to drop in some additional records on the Excel report.

3. No need for the date range selection menu in the query – the two fiscal year selections (current and last year) will be done in Excel pivot table filters.

Again, thanks a million for your help!

 

[JHB]

Do you have all these report in Excel and running them from there?
If yes, then I think it is more easy to find what is the problem in Excel and maybe have some code in Excel to handle it!

 

[bimmer5]

Yes, all reporting needs to be done in an Excel pivot table.
I use the custom 'calculated fields' option to get the differences between the sales columns. When attempted to sort the report by 'this year sales' column, Excel returns a warning message (see attached) that it couldn't do it. None of the solutions I googled worked to resolve the error.

 

[JHB]

I do not use Excel very much, but can't you take away the AutoSort and do it by some VBA code? Do you have some sample data in a Excel sheet? And also info about how to reproduce the error you get.

 

[bimmer5]

I am not familiar with VBA but tomorrow I'll prepare sample data in Excel sheet and the steps on how to reproduce the error.
Thank you!

 

[bimmer5]

Hello JHB,
as discussed, attached is a sample Excel pivot table that will pretty much resemble the final report.
The requirement for this report is to compare the two latest fiscal years (in this case Fiscal 2013-2014 vs. Fiscal 2014-2015). The sales guys want to see the quantity and dollars differences and their % differences. They want to sort and rank the customers by the latest fiscal year sales (in this report by the column 'I'.) from the highest to the lowest.
Also, they want to group and subtotal the first 50 customers (not 5 as in the previous sample) from the rest.
Excel returns the previously mentioned error if I right-click any cell within the column 'I' (Fiscal 2014-2015 sales) > Sort > Sort Largest to Smallest. Replying Yes or No does not make any difference.
I understand there is another Excel 'Top N...' functionality but it doesn't give me option to select the column 'I' only. This functionality can be accessed by right clicking any Customer column (the column 'A') > Filter > Top 10...
Let me know if you have any questions.
Thank you.

 

[JHB]

Sorry - I get an error saying the pivot table is not valid, so I'm not able to reproduce the error you get.

 

[bimmer5]

Please try again using the attached file.
This one is Excel 2010 but the previous one was downgraded to 97-2003 version and probably lost some features.
Thanks.