Replacement of Array Index Small If for Huge Datas - Advanced Help Needed!

[Wrathard]

Hello guys,

I've been trying to get rid of several Index- Small - If arrays since I'm pulling data of over 100,000 rows and this causes excel to pretty much slowdown and process forever.

I've spent over 2 full days trying to figure this out to no avail. This is pretty much advanced excel, so I'll probably need the gurus to help me out here.

Basically what I want to do is the following:
​

1. Create a top 10 customer list which is sorted from highest to lowest
2. This top 10 list depends on two variables, these are listed in cell B5 and B6 respectively (Country and Product)
3. Depending on these two filters, the top 10 list automatically changes (I use arrays for this, which is what I want to change)
4. There is a helper column on tab "Data" that is being used to gather the sums of all Country and Product combinations (I use this helper to avoid using more arrays).

This is part of a dashboard I'm trying to create where users can select their country of choice and product. That is why the need of those filters.

There are only around 1500 rows in the attached file, however the real data should have around 100,000. Which would make arrays inefficient.

Please guys, let's try to figure this out together - greatly appreciate all the support.

Below the link for a test file (when I get home I'll upload in DropBox/OneDrive and update link):

https://ufile.io/gsfmi

Thanks!​

 

[PatOBrien198]

Have you tried to accomplish this with a pivot table?

 

[Wrathard]

Have you tried to accomplish this with a pivot table?

Yes, however I'd like to point out that Slicers and Pivot Table are not an optimal solution. The dashboard will use data from a lot of different tables and slicers/pivots won't work as the source data will be different.