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

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3172822%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ERearrage%20stacked%20tables%20(by%20year)%20into%20one%20table%20by%20column%20years%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3172822%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EHi%20everyone%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20many%20tables%20which%20have%20been%20outputted%20vertically%20(on%20the%20left%20in%20the%20image)%20which%20i%20need%20to%20be%20ordered%20by%20year%20as%20columns%20beside%20each%20other%20(like%20on%20the%20right%20hand%20side).%20In%20actuality%20the%20number%20of%20years%20is%20many%20more%2C%20and%20i%20have%20many%20more%20sheets%20just%20like%20this%20so%20a%20quick%2Fautomated%20solution%20to%20get%20these%20in%20order%20(like%20the%20right-hand%20side)%20would%20be%20very%20helpful.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EFor%20clarification%20'var%201%20-%2010'%20is%20the%20variable%20names%20which%20occur%20every%20year%20and%20'yrXmeanX'%20are%20the%20data%20for%20each%20variable.%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EThank%20you%2C%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EMichael%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348680i194EDD11B3584DED%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22excelexample.PNG%22%20alt%3D%22%5C%26quot%3Bexcelexample.PNG%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3172822%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Visitor

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

excelexample.PNG

1 Reply

@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

image.png