Complex Transpose and Combine

[JWPhnx]

I have a CSV where the fields are all located in one column per rows repeated unique values for each field.

I.E.

Column1                   Column2

Date                               1/1/1900

Color                               Blue

Time                              4:00

Date                               1/2/1900

Color                             Red

Time                              3:00

I need them to be consolidated and transposed to the following:

Date              Color                Time

1/1/1900       Blue                  4:00

1/2/1900       Red                   3:00

Is there a programmatic way to accomplish this in excel easily?

[#2]

HI @JWPhnx 

To unstack your data please find below solution step using Power Query, considering your ExcelTable name is Table1, you can do the following.

Option 1

You can copy the below code and paste in Power Query Advance Editor

Option 2

1. Paste the data in blue table
2. Select the Green Excel Table > right click > refresh.

This will work like magic =) 

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column1] <> null)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "LineNo", 1, 1),
    #"Added Conditional Column" = Table.AddColumn(#"Added Index", "FileNo", each if [Column1] = "Date" then [LineNo] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"FileNo"}),
    #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"LineNo"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"FileNo", "Column1", "Column2"}),
    #"Pivoted Column" = Table.Pivot(#"Reordered Columns", List.Distinct(#"Reordered Columns"[Column1]), "Column1", "Column2"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",{{"Date", type date}, {"Color", type text}, {"Time", type time}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"FileNo"})
in
    #"Removed Columns1"

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official Answer.

 

[#3]

If you find the above solution resolved your query don't forget mark as Official Answer.