#57 — Concatenate Column Titles Corresponding to Empty Cells

Problem description & analysis:

An Excel table has column titles, and there are certain empty cells.

For each row, concatenate column titles corresponding to the empty cells with slashes (/).

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.

---

The example was originally on StackOverflow. Feel free to check the conventional solutions and compare them with the SPL approach to get the efficiency of it!

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