What's the easiest way to split a list into a specific number of rows and columns?

Question

For example I have a column with the numbers 1-20, and I want to split it into 3 rows by 4 columns, then the reminder of the rows with 3 columns, while maintaining a horizontal order:

1	2	3	4
5	6	7	8
9	10	11	12

13	14	15
16	17	18
19	20

Except the list would be much longer and need to adjust the number of rows and columns constantly. In situations like this, would it be easier to write a macros, or use multiple OFFSET formula (but have to adjust the formula each time)?

Thanks in advance

lorenzo · Answer

fakewen&nbsp;Assuming you run 365 (cf. Welcome to your Excel discussion space!) and your values are in A1:A20, one way:=LET(
  range, A1:A20,
  IFERROR(
    VSTACK(
      INDEX(range, SEQUENCE(3,4)),
      INDEX(range, SEQUENCE(ROUNDUP((ROWS(range)-12)/3,0),3,13))
    ),
    ""
  )
)&nbsp;

lorenzo · Answer

fakewen&nbsp;Another option with Power Query with your values formatted as Table (named Table1 below)&nbsp;let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    FromRows = (list as list, columns as number) as table =&gt;
        Table.Combine(
            List.Transform(
                List.Split(list, columns),
                each Table.FromRows({_})
            )
        ),

Result = Table.Combine(
        {
            FromRows(Table.FirstN(Source, 12)[Values], 4),
            FromRows(Table.Skip(Source, 12)[Values], 3)
        }
    )
in
    ResultBenefit: the blank cells are really empty (null)

lorenzo · Answer

fakewenLiking posts is appreciated; Marking solution helps people who Search...

lorenzo · Answer

=IFNA(
  VSTACK(
    WRAPROWS(TAKE(A1:A20,12),4),
    WRAPROWS(DROP(A1:A20,12),3)
  ),
  ""
)

Forum Discussion

What's the easiest way to split a list into a specific number of rows and columns?

Share

Resources