#42 — Skip Empty Cells To Sort Members

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

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.

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-09T03:08:17+00:00) #42 — Skip Empty Cells To Sort Members. Retrieved from https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/

MLA

" » #42 — Skip Empty Cells To Sort Members." Judith-Excel-Sharing | Sciencx - Friday August 9, 2024, https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/

HARVARD

Judith-Excel-Sharing | Sciencx Friday August 9, 2024 » #42 — Skip Empty Cells To Sort Members., viewed ,<https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/>

VANCOUVER

Judith-Excel-Sharing | Sciencx - » #42 — Skip Empty Cells To Sort Members. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/

CHICAGO

" » #42 — Skip Empty Cells To Sort Members." Judith-Excel-Sharing | Sciencx - Accessed . https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/

IEEE

" » #42 — Skip Empty Cells To Sort Members." Judith-Excel-Sharing | Sciencx [Online]. Available: https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/. [Accessed: ]

rf:citation

» #42 — Skip Empty Cells To Sort Members | Judith-Excel-Sharing | Sciencx | https://www.scien.cx/2024/08/09/42-skip-empty-cells-to-sort-members/ |

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.