DEV Community

Cover image for #49 - Group An EXCEL Table And Concatenate Values of Rows Meeting The Specified Condition
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#49 - Group An EXCEL Table And Concatenate Values of Rows Meeting The Specified Condition

Problem description & analysis:

An Excel table has four columns, among which the 2nd one is the grouping column:

original table

We want to group rows of the table by the 2nd column, from each group find rows where values in the 4th column are "done", concatenate values of the 3rd column of these rows, and form a new table using the new column, grouping column and row number.

desired table

Solution:

Use SPL XLL to do this task:

=spl("=?.select(~4==$[done]).groups(~2;concat@c(~3)).(#|#1|#2)",A1:D7)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:

group()s function groups rows and handles each group; ~2 represents the 2nd child member of the current member. $[] represents a string. # is ordinal number of the current member; #1 is the 1st column of the table. concat@c concatenates members with the comma.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Download esProc Desktop for FREE and boost your productivity today! ⬇️

💧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...

Some comments may only be visible to logged-in visitors. Sign in to view all comments.