Search code examples
google-sheetsformula

How to make this formula shorter in google sheets


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.

enter image description here

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!


Solution

  • You need TEXTJOIN() and FILTER() function.

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

    enter image description here