SOLVED

Excel query with "sort" & "Filter"

%3CLINGO-SUB%20id%3D%22lingo-sub-2243754%22%20slang%3D%22en-US%22%3EExcel%20query%20with%20%22sort%22%20%26amp%3B%20%22Filter%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2243754%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20I'm%20hoping%20I%20can%20get%20some%20help%20with%20the%20following%20query.%3CBR%20%2F%3EI%20have%20been%20using%20the%20sort%20and%20filter%20function%20in%20the%20attached%20workbook%20to%20work%20out%20the%20top%205%20results%20from%20a%20list%20of%20names%20and%20scores.%3CBR%20%2F%3EInstead%20of%20finding%20the%20top%205%20results%20I%20want%20to%20be%20able%20to%20show%20the%20results%20for%20people%20who%20achieve%20a%20score%20of%205%20or%20more%20including%20any%20duplicates%20who%20score%205.%3CBR%20%2F%3EHave%20been%20struggling%20with%20this%20one%20for%20a%20couple%20of%20days%20and%20would%20appreciate%20some%20help.%3CBR%20%2F%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20formula%20I%20am%20using%2C%20what%20do%20I%20need%20to%20change%20to%20be%20able%20to%20see%20all%20records%20who%20scored%205%20or%20above.%3C%2FP%3E%3CP%3E%3DSORT(FILTER(A2%3AB12%2C%20B2%3AB12%26gt%3B%3DLARGE(B2%3AB12%2C%20E2))%2C%202%2C%20-1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2243754%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-2243790%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20query%20with%20%22sort%22%20%26amp%3B%20%22Filter%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2243790%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3BSee%20attached.%20Two%20working%20formulae%2C%20though%20I%20feel%20that%20there%20must%20be%20better%20ways%20to%20solve%20this.%20But%20I%20can't%20thinks%20of%20anything%20else%20right%20now.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSORTBY(FILTER(Table1%2CTable1%5BScore%5D%26gt%3B%3DE2)%2CFILTER(FILTER(Table1%2CTable1%5BScore%5D%26gt%3B%3DE2)%2C%7B0%2C1%7D)%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EOr%2C%20same%20as%20above%20but%20wrapped%20in%20LET%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(%0Afltr%2CFILTER(Table1%2CTable1%5BScore%5D%26gt%3B%3DE2)%2C%0Areturn%2CSORTBY(fltr%2CFILTER(fltr%2C%7B0%2C1%7D)%2C-1)%2C%0Areturn%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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)

5 Replies
best response confirmed by robwill100 (Occasional Contributor)
Solution

@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.
Cheers

@robwill100 Great! Glad I could help.

 

@Riny_van_Eekelen I hope I am not stretching my luck in asking another question.

 

I would like to add a 3rd column to be displayed when i apply the formula you provided to me. The additonal column contains values related to the person in the "name" column and the results in the "Score Column".

 

Once again appreciate your help.

@robwill100 See attached.