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