#56 — Get The Same Rows from A Table

Judith-Excel-Sharing - Sep 3 - - Dev Community

Problem description & analysis:

The Excel table below has 4 columns. If we regard columns from the 2nd to the 4th as a whole, there are duplicates, as shown below:

original table
We want to select the same rows to form a new table while retaining the original order.

desired table

Solution:

Enter the following formula in SPL XLL:

=spl("=?.group@u(~.to(2,)).select(~.len()>1).conj()",A2:D15)

As shown in the picture below:

result table with code entered

Explanation:

group()function groups the table rows; @ u option enables keeping the original order; ~ is the current member; and to(2,) gets child members of the current member from the 2nd to the last.


This case was originally on StackOverflow. The conventional solution is quite complicated, while the SPL approach is much simpler and more efficient.

Feel free to download esProc Desktop for FREE and experience the efficiency yourself:

SPL download address: esProc Desktop FREE Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

YouTube FREE courses: SPL Programming

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .