Problem description & analysis:
An Excel table has column titles, and there are certain empty cells.
A | B | C | D | E | F | |
1 | stat A | stat B | stat C | stat D | stat E | stat F |
2 | 1 | 1 | ||||
3 | X | X | ||||
4 | X | X | X | X | ||
5 | ||||||
6 | X | X | X | |||
7 | X | X | X | X | ||
8 | X | X |
For each row, concatenate column titles corresponding to the empty cells with slashes (/).
H | |
1 | Summary |
2 | stat B/stat C/stat E/stat F/ |
3 | stat C/stat D/stat F/ |
4 | stat B/stat E/ |
5 | stat B/stat C/stat D/stat E/stat F/ |
6 | stat C/stat E/stat F/ |
7 | stat B/stat F/ |
8 | stat C/stat D/stat E/ |
Solution:
Enter the following formula in SPL XLL:
=spl("=?.to(2,).(?(1)(~.pselect@a(!~)).concat($[/]))",A1:F8)
As shown in the picture below:
Explanation:
pselect()finds positions of members meeting the specified condition; by default, it returns the position of the first eligible member; @a option enables returning positions of all eligible members. to(2,) gets members of a sequence from the 2nd to the last.
For more FREE resources, please refer to the following links ⬇️:
SPL download address: esProc Desktop FREE Download
Plugin Installation Method: SPL XLL Installation and Configuration
References to other rich Excel operation cases: Desktop and Excel Data Processing Cases
YouTube FREE courses: SPL Programming