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 2nd 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 1st column or the 6th column:

A   B   C   D   E   F
1       ATLANTIC SPIRIT             
2       Looe                
3   Vessel  Species Size    Kg  Date    Location
4       POLLACK 2   2.5 23/04/2024  
5       POLLACK 3   18.8    23/04/2024  
6       POLLACK 41  5.4 23/04/2024  
7       LING    3   1.9 23/04/2024  
8       WHITING 2   0.4 23/04/2024  
9                       
10      BEADY EYE               
11      Plymouth                
12  Vessel  Species Size    Kg  Date    Location
13      BASS    4   15.7    23/04/2024  
14      BASS    5   3.2 23/04/2024  
15                      
16      BOY JACK                
17      Plymouth                
18  Vessel  Species Size    Kg  Date    Location
19      PLAICE  1   0.8 23/04/2024  
20      BLONDE RAY  1   14.3    23/04/2024  
21      BLONDE RAY  3   1.6 23/04/2024  
22      SPOTTED RAY 5   1.2 23/04/2024  
23      THORNBACK RAY   1   6.3 23/04/2024  
24      THORNBACK RAY   2   15.7    23/04/2024  
25      THORNBACK RAY   3   10.9    23/04/2024  
26      THORNBACK RAY   4   2.6 23/04/2024  
27      LOBSTER 1   2.7 23/04/2024  
28      LOBSTER 2   1.1 23/04/2024  
29      RAY BACKS   1   42.1    23/04/2024  

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

A   B   C   D   E   F
1       ATLANTIC SPIRIT             
2       Looe                
3   Vessel  Species Size    Kg  Date    Location
4   ATLANTIC SPIRIT POLLACK 2   2.5 23/04/2024  Looe
5   ATLANTIC SPIRIT POLLACK 3   18.8    23/04/2024  Looe
6   ATLANTIC SPIRIT POLLACK 41  5.4 23/04/2024  Looe
7   ATLANTIC SPIRIT LING    3   1.9 23/04/2024  Looe
8   ATLANTIC SPIRIT WHITING 2   0.4 23/04/2024  Looe
9                       
10      BEADY EYE               
11      Plymouth                
12  Vessel  Species Size    Kg  Date    Location
13  BEADY EYE   BASS    4   15.7    23/04/2024  Plymouth
14  BEADY EYE   BASS    5   3.2 23/04/2024  Plymouth
15                      
16      BOY JACK                
17      Plymouth                
18  Vessel  Species Size    Kg  Date    Location
19  BOY JACK    PLAICE  1   0.8 23/04/2024  Plymouth
20  BOY JACK    BLONDE RAY  1   14.3    23/04/2024  Plymouth
21  BOY JACK    BLONDE RAY  3   1.6 23/04/2024  Plymouth
22  BOY JACK    SPOTTED RAY 5   1.2 23/04/2024  Plymouth
23  BOY JACK    THORNBACK RAY   1   6.3 23/04/2024  Plymouth
24  BOY JACK    THORNBACK RAY   2   15.7    23/04/2024  Plymouth
25  BOY JACK    THORNBACK RAY   3   10.9    23/04/2024  Plymouth
26  BOY JACK    THORNBACK RAY   4   2.6 23/04/2024  Plymouth
27  BOY JACK    LOBSTER 1   2.7 23/04/2024  Plymouth
28  BOY JACK    LOBSTER 2   1.1 23/04/2024  Plymouth
29  BOY JACK    RAY BACKS   1   42.1    23/04/2024  Plymouth

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 6th member on the current member’s subordinate level; and m(i:) gets members from the ith to the last one.

Comments

[Judith-Data-Processing-Hacks]

What do you guys think of this solution provided by SPL XLL? Please feel free to share any comments or opinions👀. You can also click this link to try SPL XLL by yourself! 👉🏻scudata.com/download-Desktop