#57 — Concatenate Column Titles Corresponding to Empty Cells

Judith-Excel-Sharing - Sep 4 - - Dev Community

Problem description & analysis:

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

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

desired table

Solution:

Enter the following formula in SPL XLL:
=spl("=?.to(2,).(?(1)(~.pselect@a(!~)).concat($[/]))",A1:F8)

As shown in the picture below:

result table with code entered

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .