Dynamic top 10 list (3 variables needed)

[MTAL]

Hi there,

This may be a bit difficult to explain however I'm trying to create a dynamic top 10 list which will use 3 columns of data - A is Client, B is Brand and C is Total Revenue. Trying to achieve top 10 lists by Brand that display Client and Total Revenue. I've attached a dummy excel file to give you a better idea of what I'm after (I hope). I've been tinkering with the LARGE formula and an IF statement but keep getting caught up. Any help would be much appreciated!!

[Richard Buttrey]

Hi,

Seems like you need a Pivot Table - see attached. Admittedly it will give you all the results in descending order but if you do just want to see the top 10 then just hide rows 14 onwards. Use the drop down Product field to select a particular product

Being a pivot table you could also drag the Product field into the row labels area above Client and see the top values by code in a table.

[MTAL]

Thanks so much - I actually had done it in a pivot table however was trying to come up with something a bit more dynamic to avoid having to refresh the table each month. Found this in another thread and seems the formula is similar to what i want except to swap out Date for Product (with the drop down menu). However I honestly am struggling to make head or tail out of the formula to be able to duplicate it for my needs.

[reference to thread I found: http://www.excelforum.com/excel-form...ic-month.html]

[benishiryo]

hi MTAL, welcome to the forum. would your "Top 10" sheet really have the text "Product..." & "A"? or will it be just "A"? here's an array formula for B3 if it's just "A" in row 1:

Please Login or Register  to view this content.

for C3:
=LARGE(IF(Master!$B$2:$B$150=INDEX($A$1:$U$1,1+FLOOR((COLUMNS($A$1:A$1)-1)/4,1)*4),Master!$C$2:$C$150),A3)
...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

if you really have the text "Product" in front of your products, then it will be more complex. in B3:

Please Login or Register  to view this content.

in C3;

Please Login or Register  to view this content.

also array formula

paste these formulas in Column B & C first. then copy both these ranges & paste to the rest.

[MTAL]

Thank you for the welcome, benishiryo! Will try it out tomorrow with the actual data sheet. Product is indeed listed by a Letter value (like A). Looking forward to being part of the community.

[Tony Valko]

A top 10 list may have more than 10 items depending on ties.

Looking at your file you have allotted space for 10 items only. Does that mean you only want 10 items regardless of any ties?

[Richard Buttrey]

Hi,

To overcome the need to manually refresh the pivot table you could have it refresh automatically every time you activate the sheet to look at it with a Sheet Activate macro.