#42 — Skip Empty Cells To Sort Members

Problem description & analysis:

In the Excel table below, column A and column B form the content structure. The dates in column A are not arranged in chronological order.

Task: Sort dates in column A in correct chronological order while keeping the empty cells in their original positions. Column B will remain what it is.

Solution:

Use SPL XLL to do this task:

=spl("=d=E@1(?), d(p=d.pselect@a(~))=d(p).sort(),d",A2:A9)

As shown in the picture below:

Explanation:

E@1 converts a data range to a single-layer sequence. pselect@a gets positions of all members meeting the specified condition; ~ is the current member. "sequence 1(a set of positions)= sequence 2" modifies members of sequence 1 at specified positions into sequence 2.

Comments

[Judith-Excel-Sharing]

Please feel free to download esProc Desktop and learn more about how to efficiently manage and clean your Excel data⬇️

🥤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 ask questions or share your thoughts in our communities:
🧊Discord: discord.gg/hgbKEvJ4
🧊Reddit: reddit.com/r/esProc_Desktop/