Problem description & analysis:
In the Excel table below, columns from the 2nd to the 6th have empty cells.
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".
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:
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.
Top comments (2)
Please feel free to download esProc Desktop and supercharge your productivity ⬇️
🌈SPL download address: scudata.com/download-Desktop
🌟Plugin Installation Method: c.scudata.com/article/1652061135502
🌈References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
🌟SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...
Feel free to reach out and discuss more details with Excel experts:
💫Discord: discord.gg/PVyKVa2J
💫Reddit: reddit.com/r/esProc_Desktop/