13.9 Convert group formed by every N columns to multiple rows


Here below is a data table. In this table, the data are stored from the second column, and every two columns form a pair of columns (i.e., each even column and each odd column except the first column form a pair of columns, such as the second and third columns). There are a total of 4 pairs of such columns:


Now we want to group by the first column and all even columns, and then aggregate the odd columns in the group. The result should be as follows:



1 =‘Sheet1!A2:I9’
2 =A1.news(~.len()\2;A1.~(1):Micro,A1.~(#*2):Group,A1.~(#*2+1):Series).select(Group!=null)
3 =A2.groups(Micro,Group;sum(Series):Series)

A2: Expand each row of A1, and the number of rows expanded is the quotient of the number of members of the row divided by 2. In the expanded row, the first column Micro is the first member of A1, the second column Group is the #*2(th) member of A1, and the third column Series is the #*2+1(th) member of A1. The symbol # here represents the row number expanded by the row.
A3: Group A2 by Micro and Group, and calculate the sum of Series and name it Series column.

esProc Desktop and Excel Processing
13.8 Convert certain columns of the same row, as group members, to multiple rows
13.10 Convert groups to columns after grouping