Search code examples
arraysexcelexcel-formulaconcatenationtextjoin

Concatenate Values and associated Headers using TEXTJOIN

I'm currently 2 separate TEXTJOIN functions to return all values in table rows (which aren't blank) and to return the headers associated with these.

To extract values (Output1):

{=TEXTJOIN(" + ",TRUE,IF(C7:XL7<>0,C7:XL7,""))}

To extract headers (Output2):

{=TEXTJOIN(" + ",TRUE,IF(C7:XL7<>0,$C$5:$XL$5,""))}

Current outputs are something like:

name       project1      project2       Output          Output2
User1      0001                         0001            project1
User2      0002          0003           0002 + 0003     project1 + project2
User3      0001          0001           0001 + 0001     project1 + project2

When what I really want is one single output, with the value header (in round brackets), before moving onto the next column:

name       project1      project2       Output
User1      0001                         0001 (project1)
User2      0002          0003           0002 (project1) + 0003 (project2)
User3      0001          0001           0001 (project1) + 0001 (project2)

Is somehow merging the two formulas above possible to output something similar to this? Array formulas make my head spin.

Solution

  • You don't need to ARRAY enter the formula IMHO.

    You can try below approach:

    =TEXTJOIN(" + ",TRUE,C7:XL7&IF(C7:XL7<>""," ("&$C$5:$XL$5&")",""))

    PS: I have tested this formula in web-version of Office 365 where it doesn't need ARRAY entry to show the results.