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

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.

Comments

[Judith-Excel-Sharing]

You can always turn to our Excel experts for some help and tips: reddit.com/r/esProc_Desktop/

[Judith-Excel-Sharing]

Today we are sharing another Excel task. Please feel free to experience the solution by using esProc: scudata.com/download-Desktop