This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Problem description & analysis:
In the Excel table below, the 1st column is the category; columns from the 2nd to the 42nd are parallel columns of data items (below only shows some of the columns), where there are two types of values – X and null. Occasionally, there are duplicate values in a column under the same category.
Task: Group rows and combine values in each column in each group; only display one of the duplicate values if there are any.
Solution:
Use SPL XLL to do this:
=spl("=?.group(~1).(g=~,(r1=~1).(g.(~(r1.#)).ifn()))",A2:E11)
As shown in the picture below:
Explanation:
group()function groups data and computes data in each group. ifn() function returns the first non-null member in the sequence, and it returns null if each member of the sequence is null. ~ is the current group; ~1 represents the 1st row of the current group; and # is the ordinal number of the current member.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-08-13T06:44:34+00:00) #44 — Group Rows And Combine Non-Null Values in Each of The Non-Grouping Columns. Retrieved from https://www.scien.cx/2024/08/13/44-group-rows-and-combine-non-null-values-in-each-of-the-non-grouping-columns/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.