This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
We have an unordered Excel table, where the 1st column is the grouping column and the 2nd column contains dates.
Task: Get rankings of rows in each group and write them in the 3rd column while retaining the existing order of the rows. The expected result is as follows:
Solution:
Use SPL XLL to do this:
=spl("=E(?).derive(#:id,rk).sort(#1,#2).run(rk=rank(#2;#1)).sort(id).(rk)",A1:B14)
As shown in the picture below:
Explanation:
E()function converts the Excel data range to a table. derive() function adds a new column. rank() function gets rankings of records in each sorted group. # is the row number and #1 represents the 1st column.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-26T07:05:48+00:00) #51 - Get Rankings of Excel Rows in Each Group While Retaining the Existing Order. Retrieved from https://www.scien.cx/2024/08/26/51-get-rankings-of-excel-rows-in-each-group-while-retaining-the-existing-order/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.