...

Expand multiple columns horizontally

 

Example

The Excel file book1.xlsx stores a variety of parts and their metal subparts in an orderly manner. When Level=2, it means that the row is a part(summary row), and when Level=3, it means that the row is a sub-part. The Material column and the Proportion column respectively store the metal name and proportion of the sub-parts. As follows:

Put the percentage of each metal on the right side of the part (summary row), as shown below:

Write SPL script:

A

1

=T("e:/work/book1.xlsx")

2

=A1.id(Material).select(~)

3

=A1.derive(${A2.string()})

4

=A3.group@i(Level==2)

5

=A4.run(~.to(2,).groups(Material;sum(Proportion):value).run(A4.~(1).field(Material,value)))

6

=T("e:/work/book2.xlsx",A3)

A1 Read in book1.xlsx file data 

A2 Select the material type that is not repeated and not empty 

A3 Add one column for each material selected in A2 

A4 Group A3, and when Level is 2, add a new group. Option @i means to add a new group when the condition is met 

A5 Loop each group of A4, and use the second to last record to calculate the sum of the proportions of each material grouped by Material and name it the value column. Loop each group, and assign the value of the Material field of the first record of the current group in A4 to value

A6 Save the results in A3 to the file book2.xlsx

 
 •  339 View  •