RaqForum 41 No.
246 View  • 
     ...

Group columns of an Excel table and perform aggregation

 

In the Excel table below, there are multiple duplicate columns.


 A B C D E F G H


Alpha Alpha Beta Beta Alpha Delta Beta
1 A 0.56 0.1 0.55 0.74 0.38 0.02 0.44
2 B 0.06 0.32 0.22 0.55 0.9 0.7 0.38
3 C 0.16 0.08 0.63 0.9 0.7 0.78 0.2
4 D 0.8 0.92 0.03 0.57 0.48 0.52 0.28
5 E 0.47 0.21 0.81 0.4 0.68 0.8 0.13
6 F 0.39 0.19 0.76 0.88 0.69 0.81 0.81

We need to group the table by columns and sum values in each group.


 A B C D
10
Alpha Beta Delta
11 A 1.04 1.73 0.02
12 B 1.28 1.15 0.7
13 C 0.94 1.73 0.78
14 D 2.2 0.88 0.52
15 E 1.36 1.34 0.8
16 F 1.27 2.45 0.81

Use SPL XLL to get this done:

=spl("=E@2bp(E@bp(?).groups(#1;${(?.len()-1).(eval@s($[sum(#?)],#+1)).concat@c()}))",B1:H7)

Picture1png

E()function converts an Excel table to a two-layer sequence; @p option enables a transposition, @b means not converting the titles, and @2 represents a two-layer sequence. groups() performs grouping and sum; ${} treats a string as an expression to execute. eval@s() loops each string in a sequence to replace them and takes them as an expression to execute. concat@c concatenates members of the sequence using the comma.

Souce:https://stackoverflow.com/questions/78161587/return-a-two-dimensional-array-using-a-sum-product-formula-excel


 
 •  246 View  •