#52 β€” Get The Last Row of Each Group

judith677

Judith-Excel-Sharing

Posted on August 27, 2024

#52 β€” Get The Last Row of Each Group

Problem description & analysis:
An Excel table has three columns, where ID is the grouping column, as shown below:

original table
Task: Find the row having the largest DATE value (the latest date) from each group and retrieve its ACTION column value. Below is the expected result:

desired table
Solution:
Use SPL XLL to get this done:

=spl("=E(?).groups(ID; maxp(E(DATE)).ACTION:ACTION)",A1:C7)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:
E()function parses an Excel data range and Excel date format. groups() function performs grouping and aggregation. maxp() function finds the position of the row having the largest value.

πŸ’– πŸ’ͺ πŸ™… 🚩
judith677
Judith-Excel-Sharing

Posted on August 27, 2024

Join Our Newsletter. No Spam, Only the good stuff.

Sign up to receive the latest update from our blog.

Related