Conditional Ranking/Display top 3 items for each group

[Novis]

Hi,

I am trying to find out what formula i can use to obtain the top 3 items for each group, dynamically. Basically the top 3 items would automatically change based on a given criteria/filtered condition. Attached is what the data look like. The yellow box would be where the top 3 items would be displayed.

I keyed in what the results would look like for the first group (Group A) to give you an idea of what I am after.

Thanks for all your help.

[rylo]

Hi

What are the rules for prioritising your models within the SALES areas? M3 is positioned 2nd for sales1 even though it is the 3rd in results?

rulo

[Novis]

There are several rules but in the end, the table i posted is the result of a pivot table sorted by "Make". M3 is listed second because the ranking is based on "sales 2" numbers.

Thanks.

[Novis]

group = region

[rylo]

Hi

So if you have 6 different makes across the 2 sales blocks, what are the rules for sorting? Or doesn't it really matter as the sorting will all be done separately later?

rylo

[Novis]

sorting doesn't matter because the table you see is really the result of a pivot table. I can switch the pivot table around to sort based on anything. What I am trying to do is to get a quick summary/snapshot of the top 3 by region based on "sale 2" numbers. Everytime i would change the region using the drop down/combo box filter, the "formula" would look for the "region" in the pivot table and then retrieve the top 3 "sales 2" numbers for that corresponding region. Then may be i can add some type of lookup formula for the number to display the corresponding model/make next to it.

I am thinking or a combination of maybe rank or large functions with IF statement but i can get the syntax to work with the filter. It works for all the data but the result doesn't change when i change the region using the drop down.

thanks.

[rylo]

Hi

OK Try this, based on your example file
J10: =INDEX(C:C,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),1),$E:$E,0))
J11: =INDEX(C:C,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),2),E:E,0))
J12: =INDEX(C:C,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),3),E:E,0))
K10: =INDEX(D:D,MAX(IF(($A$4:$A$43=$J$1)*($C$4:$C$43=J10),ROW($C$4:$C$43),""))) Copy down to K12.
L10: =INDEX(E:E,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),1),$E:$E,0))
L11: =INDEX(E:E,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),2),$E:$E,0))
L12: =INDEX(E:E,MATCH(LARGE(IF($A$4:$A$43=$J$1,$E$4:$E$43,""),3),$E:$E,0))

Note that all these are array entered formulas (ctrl, shift, enter).

rylo

[Novis]

Thank you very much. It works. Sorry for the delay in responding today. I had lots of meetings today and didn't get a chance to check until now. I am going to have to study the functions you used.

THANK YOU.