Have been trying to find a solution so i can import this data into my ERP We get the report in a horizontal or report format (on the left) and manually enter into our system. If we can convert into a vertical format (on the right) we can import it instead. The columns would be STYLE COLOR SIZES QTY
I found something close but i could not expand on it with my limited experience (Convert multiple rows into single column based on values)
As Ron Rosenfeld suggested, you may use Power Query to transpose your data.
Step 1: Choose the data, right-click it > Get Data from Table/Range, do not tick the box of "My table has headers" > OK.
Step 2: Go to File tab > Reduce Rows group > Remove Duplicates. This action could remove some duplicated headers from data source.
Step 3: After Step 2, the data would be divided into some types with different sizes. On my sample, there are 2 types, then I go to left Queries panel, copy the table.
Step 4: Remove extra rows and keep one size type for each query. For my sample, I go to File tab > Reduce Rows group > Keep Rows > Keep Range of Rows. As there is an extra column, I delete it in X, XS siez query.
Step 5: Set the first row as headers for each query. Select columns of size,
Step 6: Then I do unpivot for each query. Go to Transform tab > Any Colum group > Unpivot Columns.
Step 7: Rename the column names, then click File tab > Combine group > Append Queries.
Steps 8: Click Close&Load.
