#48 - In An Excel Table, Find Rows Corresponding to The 1st And The Last Non-Empty Cells in Each Column
Problem description & analysis:
In the Excel table below, columns from the 2nd to the 6th have empty cells.
| A | B | C | D | E | F | |
| 1 | StartDate | QID1 | QID2 | QID3 | QID4 | QID5 |
| 2 | 10/03/2024 10:56 | 1 | yes | 3 | 2 | |
| 3 | 10/03/2024 03:10 | 2 | no | 4 | 23 | |
| 4 | 09/03/2024 19:25 | 3 | yes | 2 | 2 | |
| 5 | 09/03/2024 11:40 | 4 | no | 1 | yes | |
| 6 | 09/03/2024 03:55 | 5 | yes | 5 | no | |
| 7 | 08/03/2024 20:10 | 6 | ||||
| 8 | 08/03/2024 12:25 | 7 | no | |||
| 9 | 08/03/2024 04:40 | 8 | yes | |||
| 10 | 07/03/2024 20:55 | 9 | no | |||
| 11 | 07/03/2024 13:10 | 1 | no | no | ||
| 12 | 07/03/2024 05:25 | 2 | no | no | ||
| 13 | 06/03/2024 21:40 | 3 | no | no | ||
| 14 | 06/03/2024 13:55 | 4 | no | |||
| 15 | 06/03/2024 06:10 | 5 | no | 3 |
Task: From each column from the 2nd to the 6th, find the first and the last non-empty cells and concatenate the cell values they correspond in the 1st column using "to".
| H | I | |
| 1 | QID1 | 06/03/2024 06:10 to 10/03/2024 10:56 |
| 2 | QID2 | 06/03/2024 06:10 to 10/03/2024 10:56 |
| 3 | QID3 | 09/03/2024 03:55 to 10/03/2024 10:56 |
| 4 | QID4 | 06/03/2024 06:10 to 07/03/2024 13:10 |
| 5 | QID5 | 09/03/2024 03:55 to 10/03/2024 10:56 |
Solution:
Use SPL XLL to do this:
=spl("=d=E@2p(?),d.to(2,).(d(1)(~.pselect@z(~)) /""to""/ d(1)(~.pselect(~)))",A2:F15)
As shown in the picture below:
Explanation:
E@2p converts a two-layer sequence to a table sequence. pselect()function gets the position of the first member that meets the specified condition; u/z enables a search backward. to(2,) gets members from the 2nd to the last; (1) represent the 1stmember.
Please feel free to download esProc Desktop and supercharge your productivity ⬇️
SPL download address: esProc Desktop Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
SPL Programming (YouTube FREE courses): https://www.youtube.com/playlist?list=PLQeR-IhHo7qNCw6o7PW8YfHvRx8pgzZso