#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:

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:

Solution:
Use SPL XLL to get this done:

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

As shown in the picture below:

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.

Comments

[Judith-Excel-Sharing]

For more esProc Desktop-related information ⬇️

🧩SPL download address: scudata.com/download-Desktop

🧩Plugin Installation Method: c.scudata.com/article/1652061135502

🧩References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

🧩SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

[Judith-Excel-Sharing]

If you have any Excel tasks, feel free to ask away:

💡Discord: discord.gg/PVyKVa2J
💡Reddit: reddit.com/r/esProc_Desktop/