Using SORTN or VLOOKUP, we can write a formula to return month-end rows from a daily data table in Google Sheets.
In other words, we can say, to return the rows containing the last day of each month from a table, we can use the function SORTN or VLOOKUP in Google Sheets.
I am talking about an array formula. We don’t need to use it in every row or use a helper column for the same.
In our example below, the sample data in the range A1:B contains daily entries.
The expected result in the range D2:F has only the month-end rows from the daily data in A1:B.
If multiple entries fall on the end-of-the-month date, the formula will only return the last entry based on the row position.
Just go through the result and the sample data to understand this.
Find below the step-by-step instructions for writing an array formula that returns month-end rows from daily data in Google Sheets.
SORTN to Return Month-End Rows from Daily Data Table
Before starting, please ensure the data is in chronological order based on the date column.
Here are the steps to return the last day of each month from a table in Google Sheets.
We will start writing the formula for a closed range A2:B20, not A2:B. So it will be easy to learn. Later on, we can very easily modify it for an open range.
1. Adding a Date and Year Column with the Data
Logic:- Using SORTN, we will remove duplicates from the data based on an end-of-the-month column. So we require one such column with the existing table. The below formula does this.
Step_1_Formula:
=ArrayFormula({eomonth(A2:A20,0),A2:B20})Select D2:D20, go to the menu Format > Number > Custom number format, and enter mmmm-yyy in the given field. It’s optional, though.
Note:- D2:D20 acts as a month and year column.
Formula Explanation:
The EOMONTH function returns a column with the month-end dates.
To combine the sample data with this column, I have used the Curly Brackets.
I have used the ARRAYFORMULA because of the EOMONTH non-array function in the formula.
2. Sort by Month and Year (End of the Month Column ) and Then by Row
Assume we unique the above output using SORTN. If so, we will end up with a table containing the first entry of each month.
So, we should sort this data by month and year (end of the month) column in ascending order and then by row numbers from 2 to 20 in descending order.
With the help of SORT and ROW functions, we can meet our requirements.
Generic Formula: Sort(Step_1_Formula,1,TRUE,row(A2:A20),FALSE)
Step_2_Formula (based on the above generic formula):
=sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE)Note:- We can remove ARRAYFORMULA within SORT. So you won’t see it with the Step_1_Formula here.
It places the rows to extract on the top of every month’s group. Now we can extract month-end rows from daily data.
In step 3, you can learn the SORTN use with the above table.
3. SORTN to Extract Month-End Rows from Daily Data in Google Sheets
Out of the four tie modes from 0 to 3, we can use tie mode 2 in SORTN here. It’s for removing duplicate rows based on a selected column.
Generic Formula: =sortn(step_2_formula,9^9,2,1,TRUE)
Generic Formula Explanation:
9^9 – As per the present data, there are 4 rows in the result.
We can use 4 instead of 9^9 in the formula. But when we are uncertain about the number of rows in the output, it is not safe to use a fixed number like 4.
So, using 9^9 (an arbitrarily large number) is safe to return all the rows in the result.
2 – Tie mode to delete duplicates.
1 – The position of the column, based on which, we want to unique the table.
TRUE – To sort column 1 in ascending order.
Here is the formula based on the above step 3 generic formula.
Final_Formula:
=sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE)It will extract month-end rows from the daily data table in Google Sheets. Please see image # 1 above for the result.
There are three columns in the result. They are a month and year (end of the month) column, an end-of-the-month date column, and the amount column.
You can limit the number of columns in the result by using QUERY as below.
Generic Formula: =query(Final_Formula,"Select Col1,Col2,Col3")
In the SELECT clause, include only the required columns. For example, use the below formula to return the 2nd and 3rd columns.
=query(sortn(sort({eomonth(A2:A20,0),A2:B20},1,TRUE,row(A2:A20),FALSE),9^9,2,1,TRUE),"Select Col2,Col3")How Can I Adapt the Formula to an Open Range
There are two steps.
1. Make all the ranges open in the Final_formula.
2. Replace eomonth(A2:A,0) with iferror(eomonth(datevalue(A2:A),0))
So the formula for an open range will be;
=sortn(sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),9^9,2,1,TRUE)Note:- Here also you can wrap the formula with QUERY to select only the columns that you want.
Vlookup to Return Month-End Rows from Daily Data Table
It is an alternative to the above SORTN formula.
Up to step # 2, there are no changes. That means we require the Step_2_formula here.
We will use the step_2 formula as the VLOOKUP range to return month-end rows from a daily data table in Google Sheets.
Let me introduce you to the Generic formula first.
Generic Formula: =arrayformula(ifna(vlookup(search_keys,step_2_formula,3,0)))
We only require the search keys to use in the VLOOKUP. The search keys are just the unique month and year (end of the month) values.
Search_Key:
=unique(ArrayFormula(iferror(eomonth(datevalue(A2:A),0))))Now let’s write the formula by following the generic formula.
=arrayformula(ifna(vlookup(D2:D,sort({iferror(eomonth(datevalue(A2:A),0)),A2:B},1,TRUE,row(A2:A),FALSE),3,0)))
That’s all.
Thanks for the stay. Enjoy!
Hi,
Is there a solution to pull the last entry per month per user? Assuming there are multiple users.
Thanks.
Hi, Nadine,
Thanks for posting your comment. So I got a chance to revise one of my old posts 🙂
When coming back to your question, i.e., the last entry per month by a specific user, you can try the below formula.
=LET(table,A2:C,ftr,2,dt,1,CHOOSECOLS(SORTN(SORT(LET(
data,FILTER(table,CHOOSECOLS(table,ftr)="user 1"),
HSTACK(EOMONTH(CHOOSECOLS(data,dt),0),data)),
dt+1,0),9^9,2,1,1),{2,3,4}))
Pay attention to the following part at the beginning of the formula.
table,A2:C,ftr,2,dt,1,Modify it.
A2:C: the table range.
2: The column index in the range to filter (user name column)
1: The column index of the date column.
Then locate the following code in the last part of the formula.
{2,3,4}It represents columns to return starting from column 2.
My formula adds a virtual column in front of the data. We should skip it. So the numbering should start from 2.
The replace “user 1” with your specific user name.
Hi Prashanth 😊
Thank you for your great tutorial on my following topic, good day to you!