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

#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:

ABCD
11a1yet
22a2done
33a3yet
44b1done
55b2done
66b3done
77b4yet
88b5done

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.

FGH
11a2
22b1,2,3

Solution:

Use SPL XLL to do this task:

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

As shown in the picture below:

r/esProc_Desktop - Group An EXCEL Table And Concatenate Values of Rows Meeting The Specified Condition

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.

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

SPL download address: esProc Desktop Download

Plugin Installation Method: SPL XLL Installation and Configuration

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

SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso