Largest 10 items by varying categories

[Jim Wilkinson]

Hi.

I've wrestled with this for several hours so any help is much appreciated. I have a table containing 5,000 projects by value. I want to show the top 10 by Project Manager and by Financial Period. I have been asked not to use VBA or Pivots.

I have used the following formula to successfully return the top 10 from the whole list:

=INDEX($A$1:$A$5000,MATCH(1,INDEX(($B$1:$B$5000=LARGE($B$1:$B$5000,ROWS(Z$1:Z1)))*(COUNTIF(Z$1:Z1,$A$1:$A$5000)=0),),0))

Column A = JobName, Column B = Value£, Column C = Project Manager, Column D= Financial Period

I now wish to add to it the Project Manager criteria, the Financial Period criteria - and, in fact, any other from the table as required.

Or indeed, use any other technique.

Many thanks in advance

Jim Wilkinson

 

[JackDanIce]

Welcome to the board.

Why can't you use pivot tables? It would be much easier to manipulate your data and use the top 10/bottom 10 feature to derive the results you're after. If it's work, I would push back and advocate Pivot Tables by showing the final result from the pivot table.

 

[Jim Wilkinson]

Thank you. It is because the data is dynamic - the table refreshes from a sequel database and the pivot table would not refresh 'automatically' without use of VBA

 

[AlanY]

for the Project Manager, replace $B$1:$B$5000 with IF($C$1:$C$5000=$E$1,$B$1:$B$5000), where E1 field the project manager required, similar for others

=INDEX($A$1:$A$5000,MATCH(1,INDEX((IF($C$1:$C$5000=$E$1,$B$1:$B$5000)=LARGE(IF($C$1:$C$5000=$E$1,$B$1:$B$5000),ROWS(Z$1:Z1)))*(COUNTIF(Z$1:Z1,$A$1:$A$5000)=0),),0))

confirm with control-shift-enter

 

[Jim Wilkinson]

Alan - thank you for that - it works excellently for single criteria - how would I run multiples?

 

[AlanY]

Alan - thank you for that - it works excellently for single criteria - how would I run multiples?

you just keep nesting the ifs, e.g. if the Financial Period is in Column D and specified in Cell F1 then

($C$1:$C$5000=$E$1,$B$1:$B$5000) becomes

($C$1:$C$5000=$E$1,if($D$1:$D$5000=$F$1,$B$1:$B$5000)) etc

 

[Jim Wilkinson]

Genius! Thanks

 

[AlanY]

you're welcome