Unpivot/Transpose columns (without blanks)

Question

I have an asset hierarchy table in Excel:

---

For testing purposes, the table can be copied/pasted into Excel by using the text below:

Hint: Copy the text from the Stack Overflow edit mode--instead of copying from the preview mode (the text from edit mode will parse correctly in Excel).

Hierarchy Path  Spec 1  Spec 2  Spec 3  Spec 4  Spec 5  Spec 6  Spec 7  Spec 8  Spec 9  Spec 10
Passenger Car   A   B   C   D   E   F   G   H   I   J
Sport Utility Vehicle   H   I   J   K   L   M   N   O   P   
1/2 Ton Pick-Up Truck   Q   R   S   T   U   V   W   X       
3/4 Ton Pick-Up Truck   Y   Z   A   B   C   D   E           
Compact Van F   G   H   I   J   K               
Cargo Van   L   M   N   O   P                   
Light Duty Truck    Q   R   S   T   

---

Question:

For each Hierarchy Path, I want to:

• Transpose the specs
• Fill in the Hierarchy Path for each spec

Note: The number of specs varies per Hierarchy Path. I would like the number of rows to match the number of specs. In other words, I wouldn't want blank specs in the output.

Example:

---

Is there a way to do this automatically in Excel 2016?

Answer 1

You can use Power Query. Then your users can update things by selecting the Refresh option on the Data tab.

In PQ, all you need to do is

• Select the Hierarchy Path column
• **UN**pivot other columns
• Delete the resultant Attribute column

All of the steps can be done from the UI, but here is the

M-Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"    Hierarchy Path", type text}, {"Spec 1", type text}, {"Spec 2", type text}, {"Spec 3", type text}, {"Spec 4", type text}, {"Spec 5", type text}, {"Spec 6", type text}, {"Spec 7", type text}, {"Spec 8", type text}, {"Spec 9", type text}, {"Spec 10", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"    Hierarchy Path"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"})
in
    #"Removed Columns"

Answer 2

---

VBA Macro:

Sub TransposeRows()
    Dim wb As Workbook
    Dim Row As Integer, LastRowInput As Integer, LastColumnInput As Integer, LastRowOutput As Integer
    Dim Path As String, Spec As String

    Set wb = ThisWorkbook
    Set InputData = wb.Sheets("Input")
    Set OutputData = wb.Sheets("Output")

    LastRowInput = InputData.UsedRange.Rows.Count
    LastColumnInput = InputData.UsedRange.Columns.Count

    OutputData.Range("A:Z").Clear
    OutputData.Range("A1").Value = "Hierarchy Path"
    OutputData.Range("B1").Value = "Spec"

    For Row = 2 To LastRowInput
        Path = InputData.Cells(Row, 1).Value
        For Col = 2 To LastColumnInput
            Spec = InputData.Cells(Row, Col).Value
            If Spec <> "" Then
                LastRowOutput = OutputData.UsedRange.Rows.Count + 1
                OutputData.Cells(LastRowOutput, 1).Value = Path
                OutputData.Cells(LastRowOutput, 2).Value = Spec
            End If
        Next
    Next

    OutputData.Select
End Sub

---

Result:

---

Notes:

The script accommodates blank specs.

It also can handle it if the user adds/removes rows or columns.