Search code examples
excelexcel-formulapivot-table

How to create a 2D table in excel using Pivot table or formulas where the result cells are not containing only values but characters as well?

Consider the below table which has the office and home cities of 3 people mentioned.

Input Table

Input Table.

I need to create a 2D table which looks like below.

Output Table

Output Table.

I tried using Pivot table but it only displays count or distinct count and not the exact value from the cell.

Suggest me a formula or VB code to do this as I need this a lot and if i have a structure, I can replicate this for every excel file and save a lot of time. My excels have hundreds of cells and it gets complicated to do manually.

Solution

  • Many ways to do this, perhaps for readability & simplicity one could use PIVOTBY() provided using MS365 Beta Version enabled.

    enter image description here

    • Formula used in cell E1

    =PIVOTBY(A2:A7,C2:C7,B2:B7,ARRAYTOTEXT,,0,,0)

    Or, Using LAMBDA() helper function called MAKEARRAY() to create the desired data structure.

    =LET(
     _Names, A2:A7,
     _Location, B2:B7,
     _LDetails, C2:C7,
     _uniqueNames, UNIQUE(_Names),
     _uniqueLDetails, TOROW(UNIQUE(_LDetails)),
     _rows, ROWS(_uniqueNames),
     _columns, COLUMNS(_uniqueLDetails),
     _databody, MAKEARRAY(_rows,_columns,LAMBDA(r,c,IFERROR(FILTER(_Location,(INDEX(_uniqueNames,r)=_Names)*(INDEX(_uniqueLDetails,c)=_LDetails),""),""))),
     _headers, HSTACK("Names",_uniqueLDetails),
     VSTACK(_headers,HSTACK(_uniqueNames,_databody)))

    Power Query, available in Windows Excel 2010+ and Excel 365 (Windows or Mac)*.

    enter image description here

    To use Power Query follow the steps:

    • First convert the source ranges 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[#"Location Details"]), "Location Details", "Location")
in
    #"Pivoted Column"

    enter image description here

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