In Excel, insert group headers to detail data rows in each group
The Excel worksheet below contains multiple vertical subtable groups, which are separated by a blank row. In each group, the 2nd cells of the both row 1 and row 2 contain subtable group headers and row 3 contain column headers; there isn’t detail data in both the 1st column and 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 |
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)
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
