#52 — Get The Last Row of Each Group

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

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.

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

Print Share Comment Cite Upload Translate Updates

APA

Judith-Excel-Sharing | Sciencx (2024-08-27T07:52:09+00:00) #52 — Get The Last Row of Each Group. Retrieved from https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/

MLA

" » #52 — Get The Last Row of Each Group." Judith-Excel-Sharing | Sciencx - Tuesday August 27, 2024, https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/

HARVARD

Judith-Excel-Sharing | Sciencx Tuesday August 27, 2024 » #52 — Get The Last Row of Each Group., viewed ,<https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/>

VANCOUVER

Judith-Excel-Sharing | Sciencx - » #52 — Get The Last Row of Each Group. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/

CHICAGO

" » #52 — Get The Last Row of Each Group." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/

IEEE

" » #52 — Get The Last Row of Each Group." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/. [Accessed: ]

rf:citation

» #52 — Get The Last Row of Each Group | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2024/08/27/52-get-the-last-row-of-each-group/ |

Please log in to upload a file.

There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.