How to use ARRAYFORMULA to work with an array of array

Question

I'm currently working on a formula but I can't seem to make it work the way I intend to.

The column A of the spreadsheet look like something like that:

| A |
| B |
| C |
| D |
| E |

And what I am trying to do is get an output like that:

| A |
| A | B |
| A | B | C |
| A | B | C | D |
| A | B | C | D | E |

So I tried using offset, to get from the first line to the current line for each of my lines.

=ARRAYFORMULA(TRANSPOSE(OFFSET(A1;0;0;ROW(A1:A5)))

But since ROW(A1:A5) doesn't return an array the cell was just | A |
So I tried adding ARRAYFORMULA around the ROW(A1:A5) and what I go was:

| 1 |
| 2 |
| 3 |
| 4 |
| 5 |

Which is what I need! But if I add it to the original function, I still only get | A |

Is there a way for me to "force" ARRAYFORMULA to run on A1:A5 so that I get the required output?

Additionnal data

• I know this would be easier to do with a script but a script solution is not what I'm looking for. I'm trying to understand why it does that and how to prevent it.
• This formula is meant to be used inside another so I need it to be only 1 formula, I can't use the cross to expand it.
• I don't mind if the solution isn't showable (if the array superpose itself by example).
• I made a demo here.
• I also tried with INDIRECT instead of OFFSET to get the ranges and I had the same results.

Answer 1

Assuming your data is located at A1:A8 enter this formula at C1 then copy it to C2:C8:

= TRANSPOSE ( ARRAY_CONSTRAIN( $A$1:$A$8 , 1 + ROW() - ROW( $C$1 ) , 1 + COLUMN() - COLUMN( $C$1 ) ))

Answer 2

Please try, somewhere in the Row1, assuming that is where your first A is, and copied down to suit:

=SPLIT(JOIN("|",A$1:A1),"|")

(as an array formula, if you must.)

Answer 3

=ARRAYFORMULA(IF(ROW(A2:A6)>=TRANSPOSE(ROW(A2:A6));TRANSPOSE(A2:A6);))

ROW() is used to create series of vertical numbers,which are compared against the same series of numbers horizontally to create a 5x5 matrix of TRUE/FALSE,which can then easily be extrapolated to the desired output.