Apr 24 2023 02:59 AM - edited Apr 24 2023 03:05 AM
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
Apr 24 2023 03:41 AM
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))
),
""
)
)
Apr 24 2023 04:54 AM
Another option with Power Query with your values formatted as Table (named Table1 below)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
FromRows = (list as list, columns as number) as table =>
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
Result
Benefit: the blank cells are really empty (null)
Apr 25 2023 04:48 AM
Liking posts is appreciated; Marking solution helps people who Search...
May 14 2023 12:06 AM
=IFNA(
VSTACK(
WRAPROWS(TAKE(A1:A20,12),4),
WRAPROWS(DROP(A1:A20,12),3)
),
""
)