exceldataframetranspose

How can I change a single column to multiple columns in excel

I have a dataset in excel that is similar to this:

enter image description here

I am trying to transpose the Status field so each category is its own column like the following:

enter image description here

There should no longer be any duplicates for the ID#, each row should have a unique ID. I used a pivot table to get the ID#, and separate the status column into multiple columns but can’t add location or date.

enter image description here

Tips? Directions? Should I be doing this without a pivot table? The data in the images are made up, the actual dataset is much larger. Any help is appreciated.

Solution

  • If you insist for an Excel Formulas then for simplicity and sake of readability you could try using the PIVOTBY()

    enter image description here

    =PIVOTBY(CHOOSECOLS(B3:E12,1,2,4),D3:D12,E3:E12,ROWS,,0,,0)

    Or Using Power Query -- add the following in the advanced editor of a blank query, after converting the source ranges into structured references aka tables:

    enter image description here

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Merged = Table.AddColumn(Source, "Merged", each Text.Combine({Text.From([ID], "en-US"), [Location], Text.From([Date], "en-US")}, ":"), type text),
    Pivot = Table.Pivot(Merged, List.Distinct(Merged[Status]), "Status", "Merged", List.Count),
    DataType = Table.TransformColumnTypes(Pivot,{{"Date", type date}}),
    Reorder = Table.ReorderColumns(DataType,{"ID", "Location", "Cancelled", "Arrived", "Checked", "Rescheduled", "Date"})
in
    Reorder