I have a Google sheet with a list of transactions.

For each row, I've got a date, an account, and its relative balance.

What I need to get is a table based on a subset of the last balance value for each account grouped by date like the following.

The closest thing I've got is a list of all the MIN and MAX values for each account for each month but it is not returning the last value that is the accurate one I'm searching for.

Is there any way to use an aggregate function with the last value?

=QUERY(DATASET, "SELECT B, MONTH(A), MAX(C), MIN(C) GROUP BY B,MONTH(A) ORDER BY MONTH(A) DESC",1)

Thanks in advance

CodePudding user response：

This is a bit late, but assuming the dataset is in A1:C8, the following formula generates the required result table:

=arrayformula({{"Date","Account","Balance"};query(ifna(vlookup(flatten(unique(month(A2:A8))&transpose(unique(B2:B8))),{month(index(sort(A2:C8,1,0),,1))&index(sort(A2:C8,1,0),,2),sort(A2:C8,1,0)},{2,3,4},0)),"where Col1 is not null")})

Format the date column using a custom date format which only shows the month and year, otherwise you'll get full dates. This one is a bit complicated to explain, but what we're doing is to use a cross-join to create every combination of month and account type in a vertical array, then doing a VLOOKUP using this array against a reverse-sorted copy of the source table with an additional column containing the month and account concatenated to each other - so we get the most recent date for each valid combination. The IFNA and QUERY then just tidy the results up, removing the rows where the VLOOKUP didn't find a result.

CodePudding user response：

You could leverage a VLOOKUP. This is creating a key for each item that is the category|the first of the respective month (EXPENSES|44562). The lookup table starts with the same, then has the firsts on their own followed by the normal data from B2:C. This is sorted by the first column in descending order.

Since VLOOKUP returns the first match, this will return the latest value.

=ARRAYFORMULA(
  IFERROR(
   VLOOKUP(
    UNIQUE(B2:B&"|"&EOMONTH(A2:A,-1) 1),
    SORT({B2:B&"|"&EOMONTH(A2:A,-1) 1,EOMONTH(A2:A,-1) 1,B2:C},1,FALSE),
    {2,3,4},FALSE)))