Problem description & analysis:
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 |
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.
Please feel free to download esProc Desktop and explore the data processing journey ⬇️
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso