SOLVED
Home

List top 5 students in a given year and class

%3CLINGO-SUB%20id%3D%22lingo-sub-452086%22%20slang%3D%22en-US%22%3EList%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-452086%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20looking%20for%20a%20more%20elegant%20formula%20solution.%3C%2FP%3E%3CP%3EI%20want%20to%20take%20the%20top%205%20scores%20from%20a%20certain%20year%20and%20class.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20list%20the%20corresponding%20names%20of%20the%20students%20who%20got%20those%20scores.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20small%20scale%20hypothetical%20data%20set.%3C%2FP%3E%3CP%3EThe%20real%20problem%20contains%20over%2020%20fields%20of%20criteria%2C%20100's%20of%20thousands%20of%20rows%20and%20to%20be%20ranked%20top%2020%20instead%20of%20top%205%20like%20in%20the%20file.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%2C%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20duplicates%20of%20the%20same%20top%20scores.%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20solution%20formula%20I%20have%20come%20up%20with%20is%20clunky%20and%20painstakingly%20slow%20to%20run.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20Excel%20experts%20who%20can%20help%20out%2C%20I%20would%20much%20appreciate%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-452086%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-452467%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-452467%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321087%22%20target%3D%22_blank%22%3E%40Pug-Mug1%3C%2FA%3E%20This%20is%20easy%20(and%20performs%20really%20well!)%20if%20you%20use%20a%20pivot%20table%2C%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-458107%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-458107%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%2C%20I%20did%20try%20to%20use%20pivot%20tables%20but%20Sheet%202%20I%20think%20demonstrates%20my%20issue%20with%20pivot%20tables.%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20some%20reason%20the%20product%20code%20is%20grouped%20together.%20I%20want%20to%20treat%20International%20and%20Domestic%20suppliers%20of%20the%20same%20product%20as%20different%20entities.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnother%20issue%20is%20the%20product%20code%20%22WIRY%22%20is%20totally%20out%20of%20order.%3C%2FP%3E%3CP%3EI%20go%20to%20%22More%20Sort%20Options%22%20then%20select%20Descending%20by%20Discount.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20light%20you%20can%20shed%20on%20this.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-458526%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-458526%22%20slang%3D%22en-US%22%3EOn%20which%20(set%20of)%20category%20do%20you%20wish%20to%20see%20the%20top%20n%20discounts%20precisely%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-463622%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-463622%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOn%20sheet%20two%2C%20let's%20say%20I'd%20like%20to%20get%20the%20top%2010%20discounts%20and%20their%20corresponding%20qualitative%20fields%20including%20supplier%2C%20distribution%20channel%2C%20price%20guarantee%2C%20availability%20in%20different%20colours.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%2C%20the%20pivot%20table%20doesn't%20order%20these%20correctly%20which%20is%20why%20I%20have%20resorted%20to%20the%20clunky%20formula.%20I%20also%20had%20to%20create%20a%20new%20field%20which%20concatenates%20two%20fields%20to%20create%20unique%20entities%20-%20but%20I'm%20trying%20to%20avoid%20this%20if%20I%20can%20so%20that%20I%20don't%20have%20to%20add%20to%20the%20raw%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20added%20just%20two%20criteria.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-471432%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-471432%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321087%22%20target%3D%22_blank%22%3E%40Pug-Mug1%3C%2FA%3E%26nbsp%3B%3CFONT%20style%3D%22background-color%3A%20%23ffffff%3B%22%3EI%20see%2C%20this%20indeed%20does%20not%20work%20well%20in%20a%20pivottable.%3C%2FFONT%3E%20You%20could%20use%20PowerQuery%20for%20that%20(Data%20tab%2C%20Get%20%26amp%3B%20Transform%20group)%2C%20see%20attached.%20After%20entering%20new%20filter%20values%20above%20te%20green%20table%2C%20hit%20the%20Refresh%20all%20button%20on%20the%20data%20tab.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-474351%22%20slang%3D%22en-US%22%3ERe%3A%20List%20top%205%20students%20in%20a%20given%20year%20and%20class%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-474351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3Bthanks%20for%20your%20advice!%3C%2FP%3E%3CP%3EMy%20excel%20skills%20are%20yet%20to%20include%20power%20queries%20but%20looks%20like%20I'll%20get%20onto%20that%20soon.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Pug-Mug1
Occasional Contributor

Hello, 

I'm looking for a more elegant formula solution.

I want to take the top 5 scores from a certain year and class. 

I want to list the corresponding names of the students who got those scores. 

 

This is a small scale hypothetical data set.

The real problem contains over 20 fields of criteria, 100's of thousands of rows and to be ranked top 20 instead of top 5 like in the file. 

 

The problem, 

There are duplicates of the same top scores. 

The solution formula I have come up with is clunky and painstakingly slow to run.

 

Any Excel experts who can help out, I would much appreciate it.

 

6 Replies

@Pug-Mug1 This is easy (and performs really well!) if you use a pivot table, see attached.

@Jan Karel Pieterse 

Thanks, I did try to use pivot tables but Sheet 2 I think demonstrates my issue with pivot tables. 

For some reason the product code is grouped together. I want to treat International and Domestic suppliers of the same product as different entities. 

 

Another issue is the product code "WIRY" is totally out of order.

I go to "More Sort Options" then select Descending by Discount.

 

Appreciate any light you can shed on this. 

On which (set of) category do you wish to see the top n discounts precisely?

@Jan Karel Pieterse 

On sheet two, let's say I'd like to get the top 10 discounts and their corresponding qualitative fields including supplier, distribution channel, price guarantee, availability in different colours. 

 

As you can see, the pivot table doesn't order these correctly which is why I have resorted to the clunky formula. I also had to create a new field which concatenates two fields to create unique entities - but I'm trying to avoid this if I can so that I don't have to add to the raw data. 

 

I have added just two criteria.

Solution

@Pug-Mug1 I see, this indeed does not work well in a pivottable. You could use PowerQuery for that (Data tab, Get & Transform group), see attached. After entering new filter values above te green table, hit the Refresh all button on the data tab.

@Jan Karel Pieterse thanks for your advice!

My excel skills are yet to include power queries but looks like I'll get onto that soon. 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies