This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
An Excel table has four columns, among which the 2nd one is the grouping column:
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.
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:
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-22T02:28:15+00:00) #49 - Group An EXCEL Table And Concatenate Values of Rows Meeting The Specified Condition. Retrieved from https://www.scien.cx/2024/08/22/49-group-an-excel-table-and-concatenate-values-of-rows-meeting-the-specified-condition/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.