#52 β Get The Last Row of Each Group
Judith-Excel-Sharing
Posted on August 27, 2024
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.
π πͺ π
π©
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
productivity #45 β Copy Each Row N Times (N Is The Cell Value of The Specified column)
August 15, 2024