Rearrage stacked tables (by year) into one table by column years

[MichaelLind212]

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 actuality the number of years is many more, and i have many more sheets just like this so a quick/automated solution to get these in order (like the right-hand side) would be very helpful.

For clarification 'var 1 - 10' is the variable names which occur every year and 'yrXmeanX' are the data for each variable. 

Thank you,

Michael

[#2]

@MichaelLind212 

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