Google Sheets Data menu Create a filter as well as Filter views command support custom formulas. To filter max N values in Google Sheets, you can use this filter menu and in that a custom formula.
For example, I want to filter max 10 values in a column. Here the number 10 is the N and that you can also control from a cell input.
I mean you can key #10 in a cell. Simply change that number to 5 and refresh the filter to filter max 5 values. First of all, let me show you this dynamic max N filter in action.
I have 15 student names in B2:B16 and their marks in C2:C16. To make you easily understand the filter output, I have put sequential numbers from 36-50 as their marks. The control cell, I mean the Nth value is in cell E1.
You can change the N number in cell E1 to control the number of filtered rows (max rows). But when you change, you also need to go to the Filter column dropdown and to click OK. Then the output will get refreshed.
Related: Extract Top N Number of Items From a Data Range in Google Sheets.
Filter By Condition Formula to Filter Max N Values in Google Sheets
To filter, as per the above example, I have used the following custom formula in the filter menu (Date > Create a filter > Filter by condition > Custom formula).
=REGEXMATCH($B$2:$B,textjoin("|",true,ArrayFormula("^"&array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)&"$")))We want to filter the top N marks right? Then why I have applied this custom formula in the name column (cell B1) instead of the marks column?
To understand this, you must know how the above formula works. Here is that detailed step-by-step info.
How the Custom Formula Filters Maximum N Numbers – Formula Explanation
I have started the coding from the below SORTN.
Step 1: Dynamically Extract N Marks and Names
=sortn($B$2:$C,$E$1,0,2,0)
The above SORTN formula dynamically filters N marks. To filter the top 10 numbers (here marks), change the value in E1 to 10. Similarly, inputting number 2 in cell E1 will filter top 2 numbers.
To know more about SORTN and its ‘complex’ arguments, you can check these two guides.
- How to Use SORTN Function in Google Sheets to Extract Sorted N Rows.
- SORTN Tie Modes in Google Sheets – The Four Tiebreakers.
Another option to extract max 3, 5, 10 or max N numbers in Google Sheets is by using the Large function within Filter function as the criterion.
=filter(B2:C,C2:C>=large(C2:C,N))Replace N in the formula with the number you want to dynamically control the number of rows to extract. But in this tutorial, I am using the SORTN formula.
Step 2: Constrain Column Numbers in Google Sheets
As you can see, the above SORTN returns a two-column output.
We want to filter max N values in Google Sheets from the above-given two columns of data. For that now we can match either of the names or marks because we have now the marks as well as names of top N scorers.
I am going to match the names in the FIlter menu column B. So I want to remove column 2 from the SORTN output. The Array_Constrain does that.
=array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)
I have chosen the name column because, in Regexmatch function, which I am going to use later, text criteria will ‘only’ work.
If we use number criteria in Regex, we must convert that to text using To_Text function or adding white space to the number. I don’t want that extra step.
Step 3: Regex Expression to Filter Max N Values in Google Sheets
If E1 value, the N value, is 3, then the below formula would return ^Student 15$|^Student 14$|^Student 13$ as the regular expression.
=textjoin("|",true,ArrayFormula("^"&array_constrain(sortn($B$2:$C,$E$1,0,2,0),9^9,1)&"$"))The pipe symbol has been used to execute multiple matches (represents OR). The ^ and $ signs have been used for an exact match of keywords.
Must Read: Filter Out Matching Keywords in Google Sheets – Partial or Full Match.
If you do not use that exact match symbols, the Regexmatch won’t differentiate “Student 1” from “Student 15” as both matches partially.
My Regexmatch tutorial will give you some idea about this expression – How to Use REGEXMATCH Function in Google Sheets.
I have used the above as the regular expression in Regexmatch in my final formula. This way you can filter max N values in Google Sheets using the Filter Menu command. Enjoy!
Related Reading:
