Turning single column data into a grid-CodePudding

I have this data:

The grid of blue cells are created by using these formulas manually in column C:

C1: TRANSPOSE(A1:A5)
C2: TRANSPOSE(A6:A10)
C3: TRANSPOSE(A11:A15)
C4: TRANSPOSE(A16:A20)
C5: TRANSPOSE(A21:A25)
C6: TRANSPOSE(A26:A30)

I'm trying to create one formula using Arrayformula in C1 that will automatically create the blue grid with N columns - currently N being 5.

Tried multiple ways to generate the range to transpose but not getting anywhere.

Any pointers on how to go about this?

Note: This could be done using app script, but I do not want to go that route and stick with Arrayformula or whatever that works.

CodePudding user response：

See my important comment to your original post. That said, given only and exactly what you show in your post, this would achieve your goal:

=ArrayFormula(IFERROR(VLOOKUP(SEQUENCE(ROUNDUP(COUNTA(A:A)/5),5),FILTER({SEQUENCE(ROWS(A:A)),A:A},A:A<>""),2,FALSE)))

Change the number of columns in the grid by changing both instances of 5 to some other number (the same number for each instance).