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.
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-06-18T09:53:22+00:00) In Excel, Combine Multiple Detail Data Columns into One Row in Each Group. Retrieved from https://www.scien.cx/2024/06/18/in-excel-combine-multiple-detail-data-columns-into-one-row-in-each-group/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.