excelexcel-formulatranspose

Tranposing dataset in excel


May I know how to transpose this data into the format I want in excel

enter image description here

The current method I had done is manually copy and paste, but i have to convert from a 15 rows x 6 columns data into a 3 rows x 30 columns data. I have to repeat for 7 times for a case study and I have 100 different case studies.

This is part of the actual table I need:

enter image description here


Solution

  • Transform Data

    enter image description here

    =LET(data,A2:C7,result_rows,3,
        sr,ROWS(data),
        sc,COLUMNS(data),
        tc,sr*sc,
        ts,SEQUENCE(tc)-1,
        dn,tc*result_rows/sr,
    WRAPCOLS(INDEX(TOCOL(data),
        MOD(INT(ts/result_rows),sc)
            +sc*MOD(ts,result_rows)+1
            +dn*INT(ts/dn)),
        result_rows))
    

    enter image description here