How to Stack Uneven Excel Columns Without VSTACK
Question details
The user needs a method to combine multiple uneven columns into a single dynamic vertical list in Excel without using the newer VSTACK function.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Flattening a source range of uneven columns into one column in legacy or specific Excel versions lacking the VSTACK function.
- Observed behavior
- The user wants to achieve a stacked list that excludes blank cells by utilizing alternative array formulas like FILTERXML and SUBSTITUTE.
Ensure your Excel version supports the FILTERXML function, which is available in Windows versions of Excel 2013 and later. Note that FILTERXML is a Windows-specific function and is not supported on Excel for Mac or Excel for the Web.
Use FILTERXML and SUBSTITUTE to Flatten the Range
Create a dynamic array formula that converts the uneven range into an XML string and parses it back into a single column, automatically ignoring blank cells.
When VSTACK is not available, you can trick Excel into treating your data range as an XML string. By using TEXTJOIN or a combination of SUBSTITUTE to wrap your cell values in custom XML tags, you can use the FILTERXML function to extract only the non-blank elements.
In the XML expression, the syntax '//y' is used to select all 'y' node elements created by the substituted text. Keep in mind that Excel's FILTERXML relies on the Windows XML parser, meaning that some advanced XPath functions like first() are not supported.
Determine the range of uneven columns you want to stack, for example, A1:C10.
Click on the cell where you want the flattened, single-column list to begin.
Type the formula combining FILTERXML and TEXTJOIN (or SUBSTITUTE) to construct the XML. For example: =FILTERXML("<x><y>"&TEXTJOIN("</y><y>",TRUE,A1:C10)&"</y></x>","//y")
Press Enter. The formula will combine the columns left-to-right, row-by-row, skipping empty cells and outputting a single dynamic vertical list.
Easily Manage and Stack Data with WPS Spreadsheet
WPS Spreadsheet offers powerful array formulas and a highly compatible environment for handling complex data transformation tasks. You can seamlessly use advanced text and lookup functions to reorganize uneven columns without compatibility headaches.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .csv spreadsheet containing the uneven columns.
- 2. Select the output location: Click on the empty cell where you want the combined vertical list to start.
- 3. Input the array formula: Type the combination of TEXTJOIN and FILTERXML formula to format your data range as an XML string.
- 4. Generate the stacked list: Press Enter to execute the formula. WPS Spreadsheet will dynamically stack your uneven columns while automatically ignoring any blank cells.
Frequently Asked Questions
Why does my FILTERXML formula return a #VALUE! error?
This error usually occurs if the generated XML string exceeds Excel's character limit for formulas (32,767 characters) or if your data contains special characters like ampersands (&) or angle brackets (<, >) that break the XML structure.
Does this FILTERXML workaround work on Excel for Mac?
No, the FILTERXML function relies on underlying Windows OS services and is not available in Excel for Mac or Excel for the Web. For Mac users without VSTACK, alternative methods like Power Query or VBA macros are required.
How does the '//y' argument work in the FILTERXML function?
The '//y' argument is an XPath expression. It tells the FILTERXML function to search the generated XML string and extract the contents of every '<y>' tag, regardless of where it appears in the XML hierarchy, returning them as an array.
