Get the last row of each group
An Excel table has three columns, where ID is the grouping column, as shown below:
A |
B |
C |
|
1 |
ID |
DATE |
ACTION |
2 |
123 |
1/1/2024 |
A |
3 |
123 |
5/1/2024 |
B |
4 |
123 |
6/1/2024 |
C |
5 |
456 |
19/1/2024 |
D |
6 |
456 |
6/1/2024 |
E |
7 |
456 |
7/1/2024 |
F |
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:
E |
F |
|
1 |
ID |
ACTION |
2 |
123 |
C |
3 |
456 |
D |
Use SPL XLL tp get this done:
=spl("=E(?).groups(ID; maxp(E(DATE)).ACTION:ACTION)",A1:C7)
E()function parses an Excel data range and Excel date format. groups() function perfroms grouping and aggregation. maxp() function finds position of the row having the largest value.
Source:https://www.reddit.com/r/excel/comments/1ci0ay1/look_up_then_return_max_value/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
