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.
Top comments (2)
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/
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...