SOLVED

New Contributor

# Filter Function Dynamic Arrays

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.