In Excel, Insert Group Headers to Detail Data Rows in Each Group
Tags: excel, spreadsheet, tutorial, productivity
Published: 2024-05-31 08:24:40

**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:

![Table with SPL code entered](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qmpnb8p8jzgustfhpsqq.png)
**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.