How to join each row of the output of a choosecols function

[kcmuppet]

Hi

I have a CHOOSECOLS function that returns an array of two columns, and I want to merge/join each row so that I'm left with one column.

ARRAYTOTEXT and TEXTJOIN seem limited to join the whole lot into one cell, which I don't want.

So far all I can think of is joining, then splitting using TEXTSPLIT specifying no column delimiter using an arbitrary symbol like ¦.

=TEXTSPLIT(TEXTJOIN(". ",TRUE,CHOOSECOLS(array,9,5)),,"¦.")

It's not very elegant because I have to add that ¦ symbol to the text, and also it adds a space at the beginning of all rows except the first.

Is there a better way?

 

[duggie33]

I think VSTACK might do what you want.

Hope that helps,

Doug

 

[kcmuppet]

Could you perhaps give me another clue?

I thought VSTACK stacks more than one array. My CHOOSECOLS gives only one array of two columns

 

[Fluff]

How about

=BYROW(CHOOSECOLS(array,9,5),LAMBDA(br,TEXTJOIN(". ",TRUE,br)))

 

[kcmuppet]

How about

=BYROW(CHOOSECOLS(array,9,5),LAMBDA(br,TEXTJOIN(". ",TRUE,br)))

Thanks, that works perfectly. I need to read up on Lambda...

 

[duggie33]

If you use VSTACK, you would have to get each column of your CHOOSECOLS array using a LET function, VSTACK(INDEX(your_arr,,1),INDEX(your_arr,,2)). I am sure Fluff's solution is more straightforward to consume.

 

[kcmuppet]

Thanks, that works perfectly. I need to read up on Lambda...

Turns out it was BYROW I needed to investigate. In case anyone ever comes across this thread, here's a great explanation of how the solution works.

 

[Rick Rothstein]

I'm late to the thread, but here is a formula that does not involve using LAMBDA...

=LET(c,CHOOSECOLS(A1:M17,9,5),TAKE(c,,1)&"."&TAKE(c,,-1))