I have an Excel file with this data format.

Now I want to transform those data into column, like this.

How should I do that with Excel Formula or VBA scripts?

I have tried using Transpose paste but it overwrites the rows underneath.

>Solution :

Using Excel Formulas it can be accomplished like as below:

---

• Formula used in cell A6

=HSTACK(TOCOL(IFS(B2:E4<>"",A2:A4),3),TOCOL(B2:E4,1))

---

Also I had prefer using POWER QUERY. To achieve this using the said procedure, follow this simple steps using POWER QUERY window UI:

---

• First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1

---

• 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="Table_1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"SKU"}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Value", "Images"}})
in
    #"Renamed Columns"

---

---

• 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.

---

---