To limit rows, I normally use Query in Google Spreadsheets. Can we use Query to extract n first rows from each group in Sheets?
Yes! We can. You can limit n rows in each group in Query in Google Sheets.
Before getting into the details, here is one similar tutorial that some of you may find useful – How to Find the Highest N Values in Each Group in Google Sheets.
I am using the below data to explain to you how to extract the first n rows from each group in Google Sheets.
In the demo data, the column A contains the Group names and B some values.
Remember the data is already sorted by column A to arrange the group of items in order.
If your data is unsorted (not grouped) then there is a different formula. I have included that at the last part of this tutorial. But I suggest you to first learn the solution with the sorted data.
How to Extract First n Rows From Each Group
Data is Sorted by Column A:
I have a list contain the names of flowers like Orchids, Tulips, and Marigold in column A and its stem count in column B.
I want to filter the first n rows from each group of flowers. With the help of Query Limit clause we can limit the number of rows, but not in each group –Three Different Ways to Limit Number of Rows in Google Sheets Query.
In this workaround I am using Query Where clause and a running count formula.
Here is the formula that extracts the first 2 rows from each flower group. I’ve used this formula in cell D2 above.
=Query({A2:B,ARRAYFORMULA(COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A)))},"Select Col1,Col2 where Col3<=2")Filter First N Rows in Group – Formula Explanation
This section handles the above formula explanation.
Actually, we have two column data ‘physically’. But in Query, there is a third column generated by the running count formula.
The running count populates sequential numbers 1, 2, 3 … that restarts from counting from 1 in each change in the group.
If you enter the running count in a blank cell (for example in cell C2) to test, the result would be as follows.
Want to know more about the above running count formula?
Must Check: Running Count in Google Sheets – Formula Examples.
I have this third column virtually preset in Query. In Query Where clause I have used the “<=” comparison operator to filter the data, i.e. Col3 <=2. That means every first two rows in each group.
Change this # 2 to the number of rows that you want to extract from each group.
To extract first n rows from each group, you can follow the above method in Google Sheets.
Extract n First Rows from Each group (Unsorted)
You can easily tweak the above formula and use it in an unsorted group of data as below.
Here is the new formula to extract the first n rows from each unsorted group in Google Sheets.
=Query({sort(A2:B,1,0),ARRAYFORMULA(COUNTIFS(sort(A2:A,1,0),sort(A2:A,1,0),ROW(A2:A),"<="&ROW(A2:A)))},"Select Col1,Col2 where Col3<=2")Here I have used the SORT function with all the data ranges used in the formula except in the ROW function.
I have virtually sorted that data using the SORT function. That’s the one and only changes in the formula.
This is the best. Thank you!
I’ve first sorted my data by group, then by value. Would there be a way to return multiple rows if there was a tie for the largest value?
It’s fine if this requires helper columns, but I would like them to dynamically grow to fit the data. I greatly appreciate your help!
Hi, David,
That seems possible with SORTN Tie Modes.
Can you create your sample data and share the sheet with me. It won’t be published.
You can leave the sample sheet URL via reply below.
Prashanth,
Col5 is the Login Time Stamp, Col1 is the Username.
I need the resulting table to show the first Login Time Stamp per Username and all dates.
So, the list should show a unique Time Stamp (Minimum) per Username and Date.
I think this is like a “Group by Date,” but I have no idea how to do it.
Thanks, I appreciate any help on it!
Hi, Jose,
I’m not clear.
If you want to group data, there should be an aggregation column. Also, you must convert timestamp to date.
You can start with this.
=ArrayFormula(QUERY({A1:D,{"Date";INT(E2:E)},F1:H},"Select * where Col1 is not null"))Or share a sample sheet link below.
Hey, thanks a lot for this, it’s very useful!
Would you know how I can extract the top n% values from each group?
I have a column A which contains the Group names and B some score values, and for each group name I’d like to be able to select the top 10%
best scores.
Have a great day,
Lorraine
Hi, Lorraine,
I do have a solution that using non-array formula.
I will post it soon.
In the meantime, if you share an example sheet by leaving the URL in your reply, I may be able to use that as the base/source data to write the post.
Hi, Lorraine,
See if this tutorial helps?
How to Get the Top N Percent Scores From Each Group in Google Sheets.
Is there a way to get this to work on a different sheet/tab? I have 7 columns (A:G) and I’m able to get it to work on the same sheet/tab as my source data but not on a different one. I’m adding the name of the source data sheet but I’m getting errors. Thanks.
Hi, Jason,
If your sheet name contains space, you should include the single quote surrounding the sheet name.
Eg:
Sheet Name: product summary
Range: A2:B
It should be referred to in the formula as;
'product summary'!A2:Bnotproduct summary!A2:BIf this doesn’t help, share your sheet link in reply which won’t be published (remove personal/confidential info from the sheet).
Hey!
Thanks a lot for the trick!
What if I want to combine two tabs? they have the same structure.
Thanks.
Hi, Bob,
To combine values in columns A and B in ‘Sheet1’ and ‘Sheet2’, use this formula.
=Query({Sheet1!A:B;Sheet2!A:B},"Select * where Col1 is not null")On this output, you can use my formula to extract first ‘n’ rows.
Thanks
That for column A:B. What about I have columns A, B, C, D, and E?
=Query({sort(A2:E,1,0),ARRAYFORMULA(COUNTIFS(sort(A2:A,1,0),sort(A2:A,1,0),ROW(A2:A),"<="&ROW(A2:A)))},"Select Col1,Col2,Col3,Col4,Col5 where Col6<=2")