Order Data Set by name

[kamaransheikh]

Can someone please help me with this query:


I have the following data set, the first row is the week number, the first column is the product. So therefore we have sales by week.

excel.jpg

I want a formulae where I can edit the week number in cell B11 and have returned the top 5 selling fruits of that week, not just the top 5 values from the column.

So my results where B11=1, should look like:


Guava
Melons
Strawberry
Kiwi
Oranges

and NOT:

66
12
11
9
7

I guess I should also give some consideration to if the sales for 2 products were the same, in that case, it should order those results alphabetically.

Thanks Guys!

[Tony Valko]

See if this helps:

How to create a top N list

[kamaransheikh]

Thanks, but how do I get it to change the column it is checking the results against when changing the week number?

I've attached an excel file with the above example

Book1.xlsx

[Tony Valko]

Try this...

A12 header = Count

Enter this formula in B12. This will return the count of records that fall within the top 5 and accounts for ties.

=COUNTIF(INDEX(B2:D9,0,B11),">="&LARGE(INDEX(B2:D9,0,B11),5))

A14 header = Top 5

Enter this array formula** in B14:

=IF(ROWS(B$14:B14)>B$12,"",INDEX(A$2:A$9,MATCH(LARGE(INDEX(B$2:D$9,0,B$11)-ROW(A$2:A$9)/100000,ROWS(B$14:B14)),INDEX(B$2:D$9,0,B$11)-ROW(A$2:A$9)/100000,0)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to B21.

[kamaransheikh]

Thank You Tony!

You are amazing

[Tony Valko]

You're welcome. Thanks for the feedback!