SOLVED

Filter Function Dynamic Arrays

%3CLINGO-SUB%20id%3D%22lingo-sub-2230797%22%20slang%3D%22en-US%22%3EFilter%20Function%20Dynamic%20Arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2230797%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20large%20filter%20function%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3DSORT(FILTER(FILTER(ANALYSIS!%24A%243%3A%24E%2420000%2CIF(ANALYSIS!%24A%243%3A%24A%2420000%3D%22BE01%22%2CIF(ANALYSIS!%24D%243%3A%24D%2420000%3D%22NO%22%2CANALYSIS!%24E%243%3A%24E%2420000%2C0)%2C0)%26gt%3B%3DLARGE(IF(ANALYSIS!%24A%243%3A%24A%2420000%3D%22BE01%22%2CIF(ANALYSIS!%24D%243%3A%24D%2420000%3D%22NO%22%2CANALYSIS!%24E%243%3A%24E%2420000%2C0)%2C0)%2CInput!%24B%242))%2C%7B0%2C1%2C1%2C0%2C1%7D)%2C3%2C-1)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBackground%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20retrieving%20data%20from%20an%20SAP%20analysis%20worksheet%20as%20an%20Array.%20This%20works%20great%20by%20finding%20and%20returning%20the%20top%20n%20products%20based%20on%20sale%20volumes%20that%20are%20not%20in%20a%20catalogue.%20This%20references%20cell%20B2%20on%20sheet%20input%20which%20is%20the%20top%20n%20number%20of%20non-catalogue%20materials%20(ranging%20from%201%20-%2020).%20My%20issue%20is%20if%20I%20have%20many%20products%20with%20the%20same%20sales%20it%20will%20return%20these%20alongside%20others%20leaving%20to%20a%20large%20array%20i.e%2040%20%2B%20when%20all%20I%20want%20is%20the%20top%2020%2C%20thus%20crashing%20or%20causing%20issues%20for%20my%20graphs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EQuestion%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is%2C%20is%20there%20some%20way%20I%20can%20limit%20the%20number%20of%20rows%20shown%20to%20a%20maximum%20of%20say%2020%20or%20something.%20Perhaps%20by%20a%20further%20filter%20function...%20Similar%20to%20how%20you%20reference%20for%20only%20the%203rd%20Column%20by%20this%20notation%3A%7B1%2C0%2C0%2C0%7D...%20However%20the%20row%20number%20will%20not%20be%20the%20same%20so%20I%20can%20not%20simply%20choose%20the%20top%2020%20rows%20by%20%7B1%2C1%2C1%2C1...0%3B%201%20....)%20and%20so%20forth.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETLDR%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20constrain%20a%20maximum%20number%20of%20rows%20in%20an%20array%20from%26nbsp%3B%20a%20filter%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2230797%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2230917%22%20slang%3D%22en-US%22%3ERe%3A%20Filter%20Function%20Dynamic%20Arrays%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2230917%22%20slang%3D%22en-US%22%3ESolved!%20I%20wrapped%20an%20Index%20formula%20around%20it%20referencing%20cell%20B2%20and%20it%20worked%20a%20treat!%3CBR%20%2F%3E%3CBR%20%2F%3E%3DINDEX(SORT(FILTER(FILTER(ANALYSIS!%24A%243%3A%24E%2420000%2CIF(ANALYSIS!%24A%243%3A%24A%2420000%3D%22BE01%22%2CIF(ANALYSIS!%24D%243%3A%24D%2420000%3D%22NO%22%2CANALYSIS!%24E%243%3A%24E%2420000%2C0)%2C0)%26gt%3B%3DLARGE(IF(ANALYSIS!%24A%243%3A%24A%2420000%3D%22BE01%22%2CIF(ANALYSIS!%24D%243%3A%24D%2420000%3D%22NO%22%2CANALYSIS!%24E%243%3A%24E%2420000%2C0)%2C0)%2CInput!%24B%242))%2C%7B0%2C1%2C1%2C0%2C1%7D)%2C3%2C-1)%2CSEQUENCE(Input!%24B%242)%2C%7B1%2C2%2C3%7D)%3C%2FLINGO-BODY%3E
New Contributor

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.

1 Reply
best response confirmed by alelac99 (New Contributor)
Solution
Solved! I wrapped an Index formula around it referencing cell B2 and it worked a treat!

=INDEX(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),SEQUENCE(Input!$B$2),{1,2,3})