In Excel, Insert Group Headers to Detail Data Rows in Each Group

Problem description & analysis

The Excel worksheet below contains multiple vertical subtable groups, which are separated by a blank row. In each group, the 2^nd cells of both row 1 and row 2 contain subtable group headers and row 3 contains column headers; there isn’t detailed data in either the 1^st column or the 6^th column:

We need to insert the subtable group headers in row 1 and row 2 of each group into the 1^st column and the 6^th column respectively:

Solution:

Use SPL XLL to enter the formula below:

=spl("=t=?.group@i(!~.ifn()),k=1,t.run(t1=~(k)(2),t6=~(k+1)(2),~.m(3+k:).run(~(1)=t1,~(6)=t6),k=2),t.conj()",A1:F29)

As shown in the picture below:

Explanation:

group@i()function groups rows according to the specified condition; ifn() function returns the first non-null member; ~ represents is the current member and ~(6) represents the 6^th member on the current member’s subordinate level; and m(i:) gets members from the i^th to the last one.