Problem description & analysis:
The following Excel table has a grouping column and two detailed 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 column 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 |
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)
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.
esProc Desktop is now FREE to download and apply: http://www.scudata.com/download-Desktop