#48 - In An Excel Table, Find Rows Corresponding to The 1st And The Last Non-Empty Cells in Each Column

#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.

ABCDEF
1StartDateQID1QID2QID3QID4QID5
210/03/2024 10:561yes32
310/03/2024 03:102no423
409/03/2024 19:253yes22
509/03/2024 11:404no1yes
609/03/2024 03:555yes5no
708/03/2024 20:106
808/03/2024 12:257no
908/03/2024 04:408yes
1007/03/2024 20:559no
1107/03/2024 13:101nono
1207/03/2024 05:252nono
1306/03/2024 21:403nono
1406/03/2024 13:554no
1506/03/2024 06:105no3

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".

HI
1QID106/03/2024 06:10 to 10/03/2024 10:56
2QID206/03/2024 06:10 to 10/03/2024 10:56
3QID309/03/2024 03:55 to 10/03/2024 10:56
4QID406/03/2024 06:10 to 07/03/2024 13:10
5QID509/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:

r/esProc_Desktop - In An Excel Table, Find Rows Corresponding to The 1st And The Last Non-Empty Cells in Each Column

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