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.
Top comments (1)
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