SOLVED

Join names (TEXTJOIN?) with one-cell 2D spilled formula

Brass Contributor

Below is a snippet of a list of names that follows some pre-specified criteria:

 

2D_Ind_Name_List.png

 

How I got there doesn't really matter, but the fact that I was able to get to this point via a dynamic formula under cell L57 that 2D spills all results down and across through R58 as highlighted by the blue-shaded area above.

 

My goal is to consolidate on row 60 the above list under a one-row list, and if any column has more than one name then all names be placed under the same cell and separated by "&" (i.e.: "Paul & Joe" on cell L60, "Ringo" on cell M60, and so on...).

 

So far, I was able to get there by use of the TEXTJOIN function, as it can be seen on the screenshot below:

 

1D_Names.png

 

The only problem with the above approach is that it must be done to each individual cell, so I need to drag it across row 60 through cell R60 to get the desired results. But that won't work for me as in real life this list may grow down and across in the future.

 

What I need is a solution that will get to the same one-row list in red but using one single-cell 2D dynamic formula on cell L60 that will always pick up all names regardless of size.

 

I've tried using TEXTJOIN, but when I do that =TEXTJOIN(" & ",,$L$57#) the result I end up getting is one single list of names separated by "&" (Paul & John & Ringo & George & Bill & Max & Mary & Ann & John) and that is not what I want.

 

Any help is appreciated...

4 Replies
best response confirmed by leolapa (Brass Contributor)
Solution

@leolapa 

=BYCOL(L57:R58,LAMBDA(col,TEXTJOIN("&",,col)))

You can try this formula.

textjoin.JPG 

=INDEX($L$57#,1,) & IF(LEN(INDEX($L$57#,2,)), " & " & INDEX($L$57#,2,), "")
or
=TAKE($L$57#,1) & IF(LEN(TAKE($L$57#,-1)), " & " & TAKE($L$57#,-1), "")

@mtarler they're both pretty good solutions albeit only covering as far as 2 rows by X columns ranges.
I suppose a recursive LAMBDA approach on top of the INDEX option would address the issue of covering any length of rows, but @OliverScheurich's BYCOL solution above does take care of that with a much shorter formula.
Thanks a lot!
BYCOL coming to the rescue indeed @OliverScheurich, thanks so much for your help!
1 best response

Accepted Solutions
best response confirmed by leolapa (Brass Contributor)
Solution

@leolapa 

=BYCOL(L57:R58,LAMBDA(col,TEXTJOIN("&",,col)))

You can try this formula.

textjoin.JPG 

View solution in original post