Summarizing data without Pivot Table

Question

Hello All,

I have a fields Client, Supply, Quantity and Country.

I need,

Summary for top 10 Clients by country sorting based on the Quantity.

Summary for top 10 Supply by country sorting based on the Quantity.

This we can do it easily by Pivot but,

I have 80+ Countries and i need to create lots of Pivot tables, also need to make sure about the range of the pivot table every week. After refreshing the pivot i am seeing few countries wont sort properly. Also, I need only Top 10 Countries and supplies for all the countries.

Any idea to summarize this data without the pivot table.

If we need to do only with Pivot table then,

How to automatically sort descending after every refresh?

How to make sure only top 10 is only display?

How to make sure the range is covered fully in the data sheet?

Pls. help me understand to summarize this data.

Summary

I have uploaded the file in drive for your quick reference, https://docs.google.com/spreadsheets/d/17AdZB4uv6aL6MU7I6vE9avCluP2H3zXH/edit?usp=sharing&ouid=101018147687121740410&rtpof=true&sd=true

Answer 1

according to me, pivot tables would be the simplest way to go about this

Try this:

1. convert your base data into a table ... select the data, press ctrl + L, (by default, table name will be Table1)
2. change the data source of your pivots to the Table name (eg Table1)

the above will ensure that whenever data is added to the table, new data will be considered in the pivots.

Just noticed, you have mentioned that you want top clients and supplies by quantity, in the screenshot, it shows "count of client name" ... i guess you should change this to "Sum of Quantity".

Answer 2

Hello All,

As i mentioned earlier, without using the Pivot table we got a summary for top client and top Supply using the Sort, filter, Index & Match formula. I tried my best to get top 10 clients and top 10 supply but i am unable to get it. Anyone had tried the summary for top 10 using the functions instead of Pivot, pls. help me fix this. Functions i mean is using Sort, Filter, Index and Match. Pls. help me. Thanks !!!

Answer 3

Hi

Thanks for providing a workbook :)

Re. Need to make sure about the range of the pivot table every week => Use a Table instead of a Range

It's not clear to me why you need 80 Pivot Tables or something similar. Assuming you would need that the below proposal can be easily adapted to create them. Currently, you select a country in C2 and the 2 dynamic arrays in columns B-C and E-F should do what you expect. A few Get & Transform (aka Power Query) queries must be refreshed (you can do a Refresh All) to get up to date info. in the Summary tab

Corresponding workbook available here (to download - won't work in Excel Online/Web)

Answer 4

Excellent !! this is what i was expecting. I opened the excel which you shared but it was very very new for me. I never tried the power query, I am so sorry, if possible can you share any tutorial link to understand how you have created the table, queries and automated this task. It would be great if you can support me on this automation pls. Thanks a lot for your response.

Answer 5

Thanks so much.. Its very interesting, i tried to understand but finding some difficulties. The workbook which you had shared on One drive can be able to view only but i cant edit it or i cant see more options to understand. I am very closer, pls. guide me.

1. How to GET or LINK the tables 1) TopTenClients and 2) TopTenSupply & 3) Country from the Data sheet? I mean How to get the summary which you had shown on Queries Sheet. Pls. help me understand
2. I Simply created a table and given the Filter formula but it is not showing me top 10 it is showing all the clients (not top 10) for China

=FILTER(FILTER(Client,Client[Country]=S1),{1,0,1}) ==> Client is the table which has all the clients and all the countries with total. Country values i have it on Cell S1. Not sure why it is not taking top 10 and showing up all and not sorting.

I drawn a Pivot table on my data and got the summary for clients and supply as shown below and created 2 Tables (Client and Supply)