Dynamically Extract Top "N" values from Database

[G.Bregvadze]

Hi,

Currently working on the case to extract Product codes based on, for example, Top 10 values from the database, for which I use current formula:

Please Login or Register  to view this content.

However it has a limitation, it works only for Unquiet list, in other words list of products should be unique and the values should be SUMMED.


Is it possible to include in formula, or use other formula combination which will work in database with non-unique values? In other words formula should indicate distinct products sum up their respective values and then list top products.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[jason.b75]

When you post a formula with named ranges it gives us less to work with, we can't see if the range is absolute, relative or dynamic.

From what I make out from your formula, a helper column adjacent to the range 'Figures'. Assuming that the range 'Figures' refers to $B$2:$B$100 (adjust ranges as necessary) enter this into C2 and fill down

=COUNTIF(B$2:B$100,">"&B2)+COUNTIF(B$2:$B2,B2)

Then get your top 10 list by using

=INDEX(List,MATCH(ROWS($G$2:$G2),$C$2:$C$100,0))

Fill down as needed.

edit:- just re-read your question and think I might have misread part of it.

[G.Bregvadze]

Hi,

Thanks for the reply.

Please see attached sample file with sample data, result and current usage of formula

[jason.b75]

Is a pivot table an acceptable solution? It just took me 10 seconds to get your required results using one.

[José Augusto]

Hi

Consider a 'DATA' table with product and quantity, a Table 'List_of_Products' as a list of unique names of products.

A Top_table can be created using the following array formula:

Please Login or Register  to view this content.

I think you'd be better off using a PivotTable to summarize your data table.

See the sample file

[G.Bregvadze]

Your formula works partially, it can be achieved via the formula I posted in the beginning, but it gives result, based on the max value of duplicate values, what we need is to sum up values of duplicate values and then make a list.

We can get result via Pivot table, but I chose formula so to make the result dynamic.

[jason.b75]

Setting the source as a data table will make it dynamic. Then use a worksheet change event to force refreshing if required.