RaqForum 41 No.
250 View  • 
     ...

In Excel, combine multiple detail data columns into one row in each group

 

The following Excel table has a grouping column and two detail data columns.


 A B C
1 Object Name Info
2 12A Gggtz 44456
3 12A Gggtr 99987
4 12A Kkkio 66543
5 12A Bbvgf 66643
6 12A Vvvhg 888765
7 12A Fffgt 87
8 6F Gggtf 232325
9 6F Xxxde 443216
10 P23 Cccvb 5543287
11 P23 Vvvbj 5656564
12 P23 Sswec 8989764
13 P23 Llloiu 343432

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


E F G H I G K L M N O P Q
1 Object Name1 Info1 Name2 Info2 Name3 Info3 Name4 Info4 Name5 Info5 Name6 Info6
2 12A Gggtz 44456 Gggtr 99987 Kkkio 66543 Bbvgf 66643 Vvvhg 888765 Fffgt 87
3 6F Gggtf 232325 Xxxde 443216
4 P23 Cccvb 5543287 Vvvbj 5656564 Sswec 8989764 Llloiu 343432

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)

Picture2png

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

Source:https://www.reddit.com/r/excel/comments/1d5ntg2/how_to_copy_data_from_lines_to_rows_but_keep_it/


 
 •  250 View  •