Forum Discussion
Excel query with "sort" & "Filter"
Hi All, I'm hoping I can get some help with the following query.
I have been using the sort and filter function in the attached workbook to work out the top 5 results from a list of names and scores.
Instead of finding the top 5 results I want to be able to show the results for people who achieve a score of 5 or more including any duplicates who score 5.
Have been struggling with this one for a couple of days and would appreciate some help.
Cheers
This is the formula I am using, what do I need to change to be able to see all records who scored 5 or above.
=SORT(FILTER(A2:B12, B2:B12>=LARGE(B2:B12, E2)), 2, -1)
robwill100 See attached. Two working formulae, though I feel that there must be better ways to solve this. But I can't thinks of anything else right now.
=SORTBY(FILTER(Table1,Table1[Score]>=E2),FILTER(FILTER(Table1,Table1[Score]>=E2),{0,1}),-1)Or, same as above but wrapped in LET:
=LET( fltr,FILTER(Table1,Table1[Score]>=E2), return,SORTBY(fltr,FILTER(fltr,{0,1}),-1), return )
5 Replies
robwill100 See attached. Two working formulae, though I feel that there must be better ways to solve this. But I can't thinks of anything else right now.
=SORTBY(FILTER(Table1,Table1[Score]>=E2),FILTER(FILTER(Table1,Table1[Score]>=E2),{0,1}),-1)Or, same as above but wrapped in LET:
=LET( fltr,FILTER(Table1,Table1[Score]>=E2), return,SORTBY(fltr,FILTER(fltr,{0,1}),-1), return )- Thanks for your help, thats doing exactly what I wanted it to do.
Cheersrobwill100 Great! Glad I could help.
