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.
