...

Concatenate column titles corresponding to empty cells

 

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/

Enter the following formula in SPL XLL:

=spl("=?.to(2,).(?(1)(~.pselect@a(!~)).concat($[/]))",A1:F8)

Picture1png

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.

Source:https://stackoverflow.com/questions/68869514/how-which-formula-to-use-to-show-combine-text-results-for-false-condition-for


 
 •  59 View  •