I have a large filter function:
=SORT(FILTER(FILTER(ANALYSIS!$A$3:$E$20000,IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0)>=LARGE(IF(ANALYSIS!$A$3:$A$20000="BE01",IF(ANALYSIS!$D$3:$D$20000="NO",ANALYSIS!$E$3:$E$20000,0),0),Input!$B$2)),{0,1,1,0,1}),3,-1)
Background:
This is retrieving data from an SAP analysis worksheet as an Array. This works great by finding and returning the top n products based on sale volumes that are not in a catalogue. This references cell B2 on sheet input which is the top n number of non-catalogue materials (ranging from 1 - 20). My issue is if I have many products with the same sales it will return these alongside others leaving to a large array i.e 40 + when all I want is the top 20, thus crashing or causing issues for my graphs.
Question:
My question is, is there some way I can limit the number of rows shown to a maximum of say 20 or something. Perhaps by a further filter function... Similar to how you reference for only the 3rd Column by this notation:{1,0,0,0}... However the row number will not be the same so I can not simply choose the top 20 rows by {1,1,1,1...0; 1 ....) and so forth.
TLDR:
How can I constrain a maximum number of rows in an array from a filter function.
