Consider the below table which has the office and home cities of 3 people mentioned.
Input Table
.
I need to create a 2D table which looks like below.
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.
Many ways to do this, perhaps for readability & simplicity one could use PIVOTBY() provided using MS365 Beta Version enabled.
• 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)*.
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"
- 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.
0