Forum Discussion

MichaelLind212's avatar
MichaelLind212
Copper Contributor
Feb 16, 2022

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 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

excel
Formulas and Functions

1 Reply

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Feb 16, 2022

    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

Resources