Search code examples
excelexcel-formulatranspose

Will transpose help me in this problem? Or is there another function

Hey guys I will post my question as pic to be clear I need solution for this in excel or python

enter image description here

Solution

  • Many ways of doing this, one of the neat ways of achieving the desired output is using Power Query the following outlines both using Excel Formulas as well as using Power Query:

    enter image description here

    • Formula used in cell A7

    =LET(
     _Data, A2:C5,
     _Week, TAKE(_Data,,1),
     _Uniq, UNIQUE(_Week),
     _Matrix, INDEX(_Data,,2),
     _UniqT, TOROW(UNIQUE(_Matrix)),
     _Transform, MAKEARRAY(ROWS(_Uniq), COLUMNS(_UniqT), LAMBDA(r,c,
     FILTER(TAKE(_Data,,-1), (INDEX(_Uniq,r)=_Week)*(INDEX(_UniqT,c)=_Matrix),0))),
     VSTACK(HSTACK(A1,_UniqT), HSTACK(_Uniq, _Transform)))

    However, using POWER QUERY will be the easiest and neat approach.

    enter image description here

    To perform the above task, follow the steps:

    • First convert the source range into a table and name it accordingly, for this example I have named it as Table1
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Pivoted Column" = Table.Pivot(Source, List.Distinct(Source[#"Matrix Name"]), "Matrix Name", "Value")
in
    #"Pivoted Column"
    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

    For future reference, if one has access to PIVOTBY() could take advantage of the same as well:

    enter image description here

    =PIVOTBY(A2:A5,B2:B5,C2:C5,SINGLE,,0,,0)