██FR█████ █INTELL███████████

In Excel, Combine Multiple Detail Data Columns into One Row in Each Group


This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

Problem description & analysis:
The following Excel table has a grouping column and two detailed data columns.

original table
We need to combine the two detail data columns in each group into one row and automatically generate column headers for the new columns.

desired result table
Solution:
Use SPL XLL to type in the following formula:

=spl("=d=E(?).group@o(Object).(Object|(~.conj([Name,Info]))), [$[Object]|(d.max(~.len())\2).conj([$[Name] / #,$[Info] / #])] | d",A1:C13)

As shown in the picture:

result table with code entered
Explanation:
E()function reads data from the Excel table. group@o groups rows without prior sorting. $[] represents a string, ~ is the current member, and # is ordinal number of the current member.


This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing