Posts Tagged ‘function match’

Creating a bestseller (Top 10) list with Excel

December 7th, 2009

Today we are going to create a bestseller (top 10) list. As a source material we will use a list of products with a corresponding number of products sold within a chosen period of time.

What we want at the end is to generate a list of top 10 selling products. To make it slightly more difficult we want this list to automatically update every time the number of products sold changes, and – just for fun – we don’t want to use VBA macros.

Creating a bestseller (top 10) list in Excel

First, let’s sort (descending order) all the sale figures and choose 10 best ones. To do so I decided to use the function LARGE and an array formula entered into the range I7:I16 spanning over 10 Excel cells. Our array formula looks as follows:

=LARGE(Sheet1!C4:C19,ROW(INDIRECT("1:"&ROWS(Sheet1!C4:C19))))

Where C4:C19 is a range with our sold products amounts.

Creating a bestseller (top 10) list in Excel

As a result we get a list of top 10 sales.

Creating a bestseller list in Excel
Now, the more difficult part. How to assign product names to the numbers ?

If we were sure that the numbers of sold products will never be the same (we don’t have any recurrent values) we could simply use INDEX and MATCH functions to match the appropriate product name to the number. Our formula could look like this:

=INDEX(Sheet1!B4:B15,MATCH(C6,Sheet1!C4:C15,0),1)

And it should work fine. But, if the sale amounts repeat, the above formula will return the same product name for each repeating number.

Excel and a bestseller list

That is not what we want. That’s why I decided to use a little different approach. To get the first product name I used the formula:

=INDEX(Sheet1!B4:B19,MATCH(F6,Sheet1!C4:C19,0),1)

And for the following product names, an array formula

=INDIRECT("Sheet1!"&ADDRESS(SMALL(IF(Sheet1!$C$4:$C$19=F7,ROW(Sheet1!$C$4:$C$19),65536),COUNTIF($F$6:F7,F7)),2))

inserted into one cell and copied over the remaining 8 cells.

Excel and a bestseller list

As you can see on the animation in the beginning of this post, the solution seems to work just fine. Every change in the sold products amount, results automatically in an adjusting change on our TOP 10 list. Enjoy!

Marcin