Apr 14 2019 10:18 PM
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.
Apr 15 2019 12:34 AM
@Pug-Mug1 This is easy (and performs really well!) if you use a pivot table, see attached.
Apr 15 2019 07:46 PM
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.
Apr 15 2019 11:47 PM
Apr 16 2019 04:05 PM
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.
Apr 18 2019 06:26 AM
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.
Apr 19 2019 12:36 AM
@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.
Apr 18 2019 06:26 AM
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.