To filter the min or max value record/row in each group in Google Sheets, we can use a formula or the DATA > CREATE A FILTER menu.

We can group the data based on one column or two columns (multiple columns).

Please see the below image. I have separated each group by a bold line.

Which method should I follow – The formula or the menu command?

Formula:- When we want to filter the min or max rows/records to a new area/range. We can use the Query function.

Filter Menu:- When we want to filter the data in the existing range. In this case, we may require to use a helper column.

I have sorted the rows based on Items in Table 1 (A2:B10).

In Table 2 (A14:C22), I have sorted the rows based on Items and Dates.

Do that necessary?

Nope! To filter Min or Max value in each group in Google Sheets, we can use unsorted tables also.

How to Filter Min or Max Value in Each Group in Google Sheets?

Formula Based Approach

We have two tables.

Table 1 in A2:B10 contains the receipt of items.

Table 2 in A14:C22 contains the date-wise receipt of items.

Before going to the Filter menu, let’s write the Query formulas first.

Formula to Filter Min Value in Single or Two Column Group

Min Formula 1 (E2):

=query({A2:B10},"Select Col1,min(Col2) group by Col1",1)

Min Formula 2 (E14):

=query({A14:C22},"Select Col1,Col2,min(Col3) group by Col1,Col2",1)

In the above formulas, other than the aggregation function MIN, we have used two Query Clauses. They are SELECT and GROUP BY.

You May Like:- How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

In the SELECT clause, we should select the columns to group the data.

The same columns should be used in the GROUP BY clause.

Formula to Filter Max Value in Single or Two Column Group

Replace Min with Max. That’s what we want to do here.

Max Formula 1 (E2):

=query({A2:B10},"Select Col1,max(Col2) group by Col1",1)

Max Formula 2 (E14):

=query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2",1)

Customizing the Field Labels

The above output contains field labels that you can entirely remove or customize.

Replace the formula in E2 with the below one.

=query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)

Regarding the E14 Query, use this one.

=query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2 label Col1'',Col2'',max(Col3)''",1)

The above formulas remove the field labels in the Query results. To add a custom label, you should put then within the single quote.

I mean to replace Col1'' with Col1'Item_Name' to get “Item_Name” as the field label of the first column.

Related:- Understand the Label Clause in Google Sheets Query.

We have learned how to find Min or Max value in each group in Google Sheets.

Filter Menu to Filter Min or Max Value in Each Group in Google Sheets

In the above examples, we have used two Query formulas – One in E2 and the other in E14.

The E2 formula returns two columns, whereas the E14 returns three columns.

We will reformat the E2 formula to return a single column TRUE or FALSE Boolean values in C2:C10 as per the image below.

We will do the same with the E14 formula (D14:D22).

Then filter the TRUE values in C2:C10 or D14:D22.

For that, we can use either of the two FILTER menu commands – CREATE A FILTER or FILTER VIEWS.

How?

1. Select C2:C10.
2. Go to Data > Create a Filter
3. Click the drop-down in C2.
4. Uncheck FALSE and click OK.

This way, we can use the menu command to filter Min or Max value in each group in Google Sheets.

Here is how to do that step by step. I mean reformatting the E2 and E14 formulas.

Note:- We will use the above two query formulas that contain the LABEL clause.

Steps

I am starting with the E2 formula.

1. Transpose E2 formula.

=transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1))

2. Then wrap it using Query to combine values.

=query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9)

Related:- The Flexible Array Formula to Join Columns in Google Sheets.

3. Apply Textjoin to place a pipe delimiter between the values.

=textjoin("|",true,query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9))

4. Use the above step # 3 formula as a regular_expression in Regexmatch.

Syntax: REGEXMATCH(text, regular_expression)

What about text argument in Regexmatch?

It’s the combined range A3:A10 and B3:B10.

={"Max";ArrayFormula(regexmatch(A3:A10&" "&B3:B10, textjoin("|",true,query(transpose(query({A2:B10},"Select Col1,max(Col2) group by Col1 label Col1'',max(Col2)''",1)),,9^9))))}

We can use the above formula in C2 to filter by max values in each group in Google Sheets.

What about the E14 formula?

The above steps are applicable there also. The only difference is in the text part in Regexmatch.

In the above example, it’s A3:A10&" "&B3:B10. Here it’s not A15:A22&" "&B15:B22&" "&C15:C22, but text(A15:A22,"dd/mm/yy")&" "&B15:B22&" "&C15:C22.

The range A15:A22 contains dates.

When we concatenate this date range with a text range, it losses the formatting. The Text function retains it.

D14 Formula:

={"Max";ArrayFormula(regexmatch(text(A15:A22,"dd/mm/yy")&" "&B15:B22&" "&C15:C22, textjoin("|",true,query(transpose(query({A14:C22},"Select Col1,Col2,max(Col3) group by Col1,Col2 label Col1'',Col2'',max(Col3)''",1)),,9^9))))}

Change Max to Min to filter by min values in each group in Google Sheets.

That’s all. Thanks for the stay. Enjoy!

Example Sheet 131021