Could anyone help tell me how to do it in EXCEL? I could use pivot sql but not sure how to make it happen in Excel.
Here is initial data
| ID | Type |
|---|---|
| 1 | Type1 |
| 1 | Type2 |
| 2 | Type1 |
| 3 | Type1 |
| 3 | Type2 |
| 4 | Type2 |
| 5 | Type1 |
| 5 | Type2 |
New data layout
| ID | Type1 | Type2 |
|---|---|---|
| 1 | Type1 | Type2 |
| 2 | Type1 | |
| 3 | Type1 | Type2 |
| 4 | Type2 | |
| 5 | Type1 | Type2 |
In powerquery, it is called Pivot Column
Bring data into powerquery (data .. from table/range)
Right click Type column and duplicate column
Click select the new duplicate column
Transform .. Pivot Column ...
For values column pick Type then in Advanced...do not aggregate
File close and load
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Type", "Type - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Type - Copy"]), "Type - Copy", "Type")
in #"Pivoted Column"