google-sheetstextjoin

Textjoin with header values


I would appreciate your assistance in resolving a small issue..

Link:

https://docs.google.com/spreadsheets/d/1_jOn0vyv6osaoppeU9TXX1boOlZbnoCFy3eWsddP7uc/edit?usp=sharing

I am using the below formula but the result is not as expected.

Formula:

=ARRAYFORMULA(JOIN(" : ",TOCOL(IF(B6:J6<>"",B5:J6,),1))) 

Result:

WHEEL LOADER : FORKLIFT : CAT 966 H /// CAT 966 G : CAT DP100NZ /// DP70NA /// DP50K1 /// CAT 966 H /// CAT 966 G /// CAT 966 F

Expected Result:

WHEEL LOADER :  CAT 966 H /// CAT 966 G /  FORKLIFT :  CAT DP100NZ /// DP70NA /// DP50K1 /// CAT 966 H /// CAT 966 G /// CAT 966 F

Solution

  • Modifying your formula to:

    =textjoin(" /  ",1,arrayformula(if(B6:J6<>"",B$5:J$5&" :  "&B6:J6,)))
    

    enter image description here