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.