RaqForum 28 No.
284 View  • 
     ...

Add Proportions of Subcategories on the Right of a Table with Group and Aggregate Vales

 
excel(115) transposition(4) conditional grouping(2)

Task description & analysis 

The following Excel table stores many types of parts and their metallic subparts. A row where Level is 2 is a summary row containing a type of part, and a row where Level is 3 contains the information of a subpart. Material column and Proportion column respectively store the metal a subpart contains and the proportion of that metal in the subpart. 

A

B

C

D

1

Name

Level

Proportion

Material

2

Part 1

2

3

Part 1 A

3

0.37 

Ally

4

Part 1 B

3

0.40 

Ally

5

Part 1 C

3

0.04 

Copper

6

Part 1 D

3

0.01 

Titainium

7

Part 1 E

3

0.04 

Steel

8

Part 1 F

3

0.07 

Titainium

9

Part 1 G

3

0.07 

Copper

10

Part 2

2

11

Part 2 A

3

0.50 

Steel

12

Part 2 B

3

0.50 

Ally

The task is to add proportion of each metal in a part on the right of the part (summary) row, as shown below:

A

B

C

D

E

F

G

H

1

Name

Level

Proportion

Material

Ally

Copper

Steel

Titainium

2

Part 1

2

0.77

0.11

0.04

0.08

3

Part 1 A

3

0.37 

Ally



4

Part 1 B

3

0.40 

Ally



5

Part 1 C

3

0.04 

Copper



6

Part 1 D

3

0.01 

Titainium



7

Part 1 E

3

0.04 

Steel



8

Part 1 F

3

0.07 

Titainium



9

Part 1 G

3

0.07 

Copper



10

Part 2

2

0.5

0.5

11

Part 2 A

3

0.50 

Steel



12

Part 2 B

3

0.50 

Ally



The algorithm involves conditional grouping, transposition, empty rows padding, and the handling of summary and detailed data.

Solution & explanation

Select the area of A1:E12 in the Excel file, copy it to cell A1 in esProc, and write the following script:

A

B

1

…（Copied data）

2

=A1.import@t()

3

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

4

=A3.(~.to(2,).groups(Material;sum(Proportion):value,count(1):rowcount))

5

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

6

=create(${A5.string()})

7

for A4

=A7.align(A5,Material)

8

=A6.record(B7.(value))

9

=A6.insert(0:B7.sum(rowcount))

A3: Group the table by part.

A4: Get rows of subparts from each group and group them by metal to calculate the proportion of each metal and the number of rows for each metal.

A5: Get the unique metals and remove rows with null values.

A6: Create an empty two-dimensional table where column names are metals to be exported.

A7: Loop through each group in A4, align rows to the metals to be exported, populate the proportions of each metal in order into A6’s table, and finally, append empty rows which is equivalent to the subparts in number.

Run the above script, copy A6’s result with the column names, and paste it to cell E1 in Excel.

Q & A Collection

https://stackoverflow.com/questions/63828183/dynamic-nested-excel-formula


excel(115) transposition(4) conditional grouping(2)
 
 •  284 View  •