Forum Discussion
MichaelLind212
Feb 16, 2022Copper Contributor
Rearrage stacked tables (by year) into one table by column years
Hi everyone, I have many tables which have been outputted vertically (on the left in the image) which i need to be ordered by year as columns beside each other (like on the right hand side). In a...
SergeiBaklan
Feb 16, 2022Diamond Contributor
Depends on your Excel version. As variant that could be
vars = UNIQUE(Table1[variable]);
yrs = TRANSPOSE(
FILTER( Table1[Year],
Table1[Year] <> ""));
varsNo = ROWS(vars);
yrsNo = COLUMNS(yrs);
data = MAKEARRAY(
varsNo,
yrsNo,
LAMBDA(r, c,
INDEX(Table1[value], r + (c - 1) * varsNo)
)
);
for