#44 — Group Rows And Combine Non-Null Values in Each of The Non-Grouping Columns

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.

Comments

[Judith-Excel-Sharing]

Feel free to reach out if you have any Excel problems and your problems may be listed in the next post of our series:

💎Discord: discord.gg/hgbKEvJ4
💎Reddit: reddit.com/r/esProc_Desktop/

[Judith-Excel-Sharing]

Feel free to download esProc Desktop and improve your productivity of data processing ⬇️

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