How to Transpose Columns into a Structured Order Sheet in Excel
Question details
The user needs to transform source data arranged in columns into a normalized, structured order-list layout while keeping item names, codes, and quantities properly aligned and filtering out blank entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Converting a two-dimensional data matrix into a flattened, three-column list format for simplified order processing, inventory tracking, or database importing.
- Observed behavior
- The current data is arranged in a grid format (columns A:C) and must be unpivoted into aligned columns (E:G) exclusively for entries with quantities, dynamically ignoring empty quantity cells.
Verify your Excel version before proceeding; dynamic array functions like LET, TOCOL, and HSTACK require Microsoft 365 or Excel for the Web. If you are using an older desktop version, you will need to use the Power Query method instead.
Use Dynamic Array Formulas (LET, TOCOL, HSTACK)
Use modern Excel array functions to automatically restructure your matrix into a clean, dynamic list without manually copying and pasting.
This method utilizes advanced dynamic arrays to define data ranges, extract headers and row codes, and filter out any blank quantities in real-time. It requires Microsoft 365.
Click on the top-left cell of your target area (for example, E3) where you want the new structured order sheet to begin.
Type the following formula: =LET(names,B3:C3,codes,A4:A8,data,B4:C8,filled,data<>"",HSTACK(TOCOL(IF(filled,names,""),2,TRUE),TOCOL(IF(filled,codes,""),2,TRUE),TOCOL(data,2,TRUE)))
Modify the 'names' (B3:C3), 'codes' (A4:A8), and 'data' (B4:C8) parameters in the formula to exactly match the header row, identifier column, and numerical matrix area of your specific source data.
Press Enter. The formula will automatically spill the data into adjacent columns and rows, perfectly aligned and with all blank quantity cells removed.
Unpivot Columns Using Power Query
Leverage Power Query to unpivot the data columns. This approach is highly reliable, excellent for large datasets, and compatible with older versions of Excel.
Easily Manage and Transpose Structured Data in WPS Spreadsheet
WPS Spreadsheet provides powerful tools for data transformation, including advanced functions and intuitive data management features, allowing you to reshape complex datasets smoothly.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your raw matrix data.
- 2. Apply dynamic array formulas: Select an empty destination cell and input your nested array formula to combine, filter, and transpose your data dynamically.
- 3. Utilize built-in data tools: Alternatively, use the comprehensive tools in the Data tab to organize, filter out blanks, and restructure your columns effectively.
Frequently Asked Questions
What if my version of Excel doesn't support the TOCOL function?
If you are using Excel 2019 or an older desktop version, the TOCOL function is not available. You should use the Power Query method (Data > From Table/Range) to unpivot the columns and filter out blank entries instead.
How does Power Query handle new data added to the original matrix?
Power Query processes data dynamically. Once you have established your unpivot steps, simply right-click anywhere inside the generated result table and select 'Refresh' to instantly update the list with your new quantities.
Why use the LET function in the array formula?
The LET function allows you to define custom names for specific ranges (such as 'names', 'codes', and 'data') directly within the formula. This not only makes the formula easier to read and modify but also significantly improves calculation speed.
Can I place the result table on a different worksheet?
Yes. Whether you are using the dynamic array formula or the Power Query output, the structured result can be placed on an entirely different sheet within the same workbook to keep your final order list separated from the raw input matrix.
