-1

I mean to concatenate several columns, so use of CONCATENATE is preferred over & (here I post an example with only 3 columns). For instance, D1 would contain =CONCATENATE(A1:C1). Then I would copy the formula down to D20 (e.g.)

        col A   col B   col C   col D
row 1   ad      fg      re      adfgre
row 2   as      we      sd      aswesd
...

Since the argument of CONCATENATE can be any (sequence of) rectangular range, it seems I cannot use in D1 =ARRAYFORMULA(CONCATENATE(A1:C20)) for the same purpose, since it understands I mean to concatenate the contents of all cells in that range, and place the result in a single cell (D1). This produces the same result as using =CONCATENATE(A1:C20) in D1, so ARRAYFORMULA has no effect here.

It seems that (roughly speaking) when combining functions (e.g., CONCATENATE) whose arguments can be rectangular ranges (and actually passing rectangular range arguments) with ARRAYFORMULA, there is an ambiguity in what one wants. So, if using ARRAYFORMULA(CONCATENATE(range)), one may want to use range as an argument for a single calculation, in which case the output of ARRAYFORMULA is a single cell, or one may want to use range as a set of "subranges" for many calculations, in which case the output of ARRAYFORMULA is a similar set of cells. As per what I found, this ambiguity is resolved in favor of the first case.

How can I use ARRAYFORMULA to instruct a function whose arguments can be rectangular ranges to actually produce a result in multiple cells, instead of applying the argument to produce a result in a single cell?

0

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(SPLIT(
 TEXTJOIN("♂", 1, {Q1:T, U1:U&"♀"}), "♀")), "♂", ""))

  • Q1:T is the full range minus last column
  • U1:U is the last column of full range
  • eg. this will concentrate each row from range Q1:U into one cell as an array
  • 1
    The objective is stated in the question at the end of the OP. I do not mean to attain the specific result of my example, I know how to do that. I do not mean to get an alternative formula for the example that involves ARRAYFORMULA. I mean to answer the generic question, which is illustrated and inspired by the example. – sancho.s Feb 22 at 15:09
  • I read your question several times and yet, I still fail to understand the purpose of it. as I understood, your end goal is to find a formula which can take a "multi-row-multi-column" range and execute joining column's content into a single column in such way that every row from the given range will be concentrated on respective row. and yet again, from some untold reason you are against using CONCAT nesting as well as formula from this answer regardless of the fact that it would bring you desired output you stated yourself. – user0 Feb 24 at 12:32
  • there just isn't a regular formula which could do what you need and look like: =UNKNOWN(A1:C20) you would need either custom formula based on script or combination of multiple regular formulas to achieve your goal. I may have 1-2 more ideas how to solve this if you are interested, but I guess you wont be satisfied with either solution... – user0 Feb 24 at 12:35
0

=ARRAYFORMULA(REGEXREPLACE(
 TRANSPOSE(QUERY(TRANSPOSE({A3:F10;"","","","","",0}),"where Col1!=0"))," ",""))

0

  • this solution exploits the broken logic of QUERY. it can take a rectangular range as an argument and doesn't lean on & nor other joining methods which are limited to a maximum of 50000 characters. the only "disadvantage" would be that if you want to join range A:K you will need to input range A:L - eg. your desired range +1 column.
0
  • simmilar approach and a bit shorter:

=ARRAYFORMULA(REGEXREPLACE(TRANSPOSE(QUERY(TRANSPOSE(A3:E11), , 50000)), " ", ""))

0

-1

=ARRAYFORMULA(CONCAT(CONCAT(A1:A, B1:B), C1:C))

  • 1
    CONCAT is equivalent to &. It is not "a function whose arguments can be rectangular ranges". How would you say this addresses the question? – sancho.s Feb 22 at 11:44
  • 1
    My end goal is stated in the boldfaced question... CONCATENATE is an example that I came across, but the question applies to many other functions for which rectangular ranges are acceptable arguments. In those cases, there would be an ambiguity in what one wants when combined with ARRAYFORMULA. – sancho.s Feb 22 at 14:59
-1
  • custom function (script) / formula

    =CC(A3:E11)

    function CC(range) {
    return range.map(function (r) {
    return [r.sort().join("")]
    })
    }


  • untested:
    function CC(range) {
    return range.map(function (r) {
    return [r.join("")]
    })
    }
  • This does not use ARRAYFORMULA as the OP means. – sancho.s Feb 26 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.