Problem description & analysis:
The following Excel table has a grouping column and two detailed data columns.
We need to combine the two detail data columns in each group into one row and automatically generate column headers for the new columns.
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:
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.