Judith-Excel-SharingProblem 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.
