Search code examples
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