Forum Discussion
List top 5 students in a given year and class
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.
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 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.
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.
JKPieterse thanks for your advice!
My excel skills are yet to include power queries but looks like I'll get onto that soon.