How to make this formula shorter in google sheets-CodePudding

I would like to know what product I exactly have with this sheet. So I add in column O for help. However, my formula seems to be too long to maintain in the future, and it will always have a "/" in the end.

This is my formula in O2

=if(isblank($A2),,$A$1&"/")& if(isblank($B2),,$B$1&"/")& if(isblank($C2),,$C$1&"/")& if(isblank($D2),,$D$1&"/")& if(isblank($E2),,$E$1&"/")& if(isblank($F2),,$F$1&"/")& if(isblank($G2),,$G$1&"/")& if(isblank($H2),,$H$1&"/")& if(isblank($I2),,$I$1&"/")& if(isblank($J2),,$J$1&"/")& if(isblank($K2),,$K$1&"/")& if(isblank($L2),,$L$1&"/")& if(isblank($M2),,$M$1&"/")&

How could I improve this forluma? Thanks!

CodePudding user response：

You need TEXTJOIN() and FILTER() function.

=TEXTJOIN("/",1,FILTER($A$1:$N$1,A2:N2=1))

CodePudding user response：

to process the whole array at one use:

=IFERROR(BYROW(A2:N, LAMBDA(x, TEXTJOIN("/", 1, FILTER(A1:N1, x<>"")))))