#51 - Get Rankings of Excel Rows in Each Group While Retaining the Existing Order

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.

Comments

[Judith-Excel-Sharing]

Download esProc Desktop and explore the data processing journey right away ⬇️

🔺SPL download address: scudata.com/download-Desktop

🔺Plugin Installation Method: c.scudata.com/article/1652061135502

🔺References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524

🔺SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

[Judith-Excel-Sharing]

Feel free to reach out if you have Excel tasks. We are HERE to help! And your tasks may be listed in the next post of the series and benefit more learners

🔸Discord: discord.gg/PVyKVa2J
🔸Reddit: reddit.com/r/esProc_Desktop/