1

I want to create sequences of cells of repeating digits using the function SEQUENCE. However I also want to gather them all in one big array using ARRAYFORMULA and then FLATTEN, but it doesn't seem to work the way I expect.

Here an example of where I use the formula SEQUENCE(1,Repeat,Value,0) to create output sequences for each individual lines. I want to produce all of these lines at once in one formula and then FLATTEN them all in one big array.

Value  Repeat  Output (over multiple columns)
 2       3       2   2   2                    
 3       2       3   3
 4       3       4   4   4
           

So I tried to write ARRAYFORMULA(SEQUENCE(1,Array_of_Repeats,Array_of_Values,0)) but this only produces the following output :

2
3
4

over a single column (no further columns are produced), so only the starting column ...

See example here

1 Answer 1

1

ArrayFormula() won't work as expected when used with functions that by default accept arrays.

Try something like this instead:

=arrayformula(split(rept(A2:A4&"❄️",B2:B4),"❄️"))

enter image description here

5
  • Ah, it is very good, that. Have a vote. However I now hit the limit of 32000 characters allowed as a result of REPT ... I still like the snowflake though.
    – Charles
    Feb 22, 2022 at 9:41
  • Try to replace the snowflake with any character that's not an emoji (as most of them have a length > 1) and see if that works.
    – user290033
    Feb 22, 2022 at 9:53
  • No I mean I definitely have to repeat things a lot more than 32000 times
    – Charles
    Feb 22, 2022 at 9:54
  • The max amount of columns is ~18k. How are you going to repeat something 32k+ times in the same row?
    – user290033
    Feb 22, 2022 at 10:00
  • I don't plan to show the data, only to do some stats on it. So whatever huge arrays are returned here will directly be fed to a function within the same formula of the same cell.
    – Charles
    Feb 23, 2022 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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