How to Use UNIQUE and FILTER with VSTACK in WPS Office Spreadsheets
Question details
The user wants to combine data from multiple sheets with VSTACK and then apply UNIQUE and FILTER to remove blanks and duplicates, and to understand why a nested formula may return an error.
- Product
- WPS Office Spreadsheets and Microsoft Excel 365
- Device & OS
- not provided
- Scenario
- The user is stacking name lists or similar data from multiple worksheets and needs one clean, unique result list without blanks or duplicates.
- Observed behavior
- The nested UNIQUE/FILTER/VSTACK formula returns an error such as #NAME?, #VALUE!, or #SPILL! instead of a clean unique list.
Before entering the formula, confirm that your spreadsheet application supports dynamic array functions—UNIQUE, FILTER, and VSTACK—and that every range used inside VSTACK has the same number of columns.
Combine Multiple Ranges with VSTACK, FILTER, and UNIQUE in One Formula
Use this nested formula when you need to stack lists from multiple sheets, remove blank cells, and return unique values in one step.
VSTACK returns one array from the stated ranges, FILTER can evaluate that same array to exclude empty strings, and UNIQUE then removes duplicates from the filtered result.
Click the blank cell where you want the unique result list to start in your WPS Office Spreadsheets or Excel 365 worksheet.
Type =UNIQUE(FILTER(VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100),VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100)<>"")) and press Enter.
If dynamic arrays are supported, the results spill into the cells below and update automatically when the source ranges change.
Fix UNIQUE/FILTER/VSTACK Errors by Checking Version, Ranges, and Source Data
Use this troubleshooting path when the combined formula returns #NAME?, #VALUE!, #SPILL!, or another error instead of a result.
Use WPS Office Spreadsheets to Create a Unique, Blank-Free List from Multiple Sheets
WPS Office Spreadsheets supports dynamic array functions, so you can enter the same VSTACK, FILTER, and UNIQUE formula to stack ranges, remove blanks, and return unique values without helper columns.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Menu > Open, and select the .xlsx workbook that contains the source sheets.
- 2. Select the result cell: Click the blank cell where you want the cleaned unique list to start, such as D2 in the summary sheet.
- 3. Enter the VSTACK formula: Type =VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100) and press Enter to check that the stacked array spills correctly.
- 4. Wrap with FILTER and UNIQUE: Change the formula to =UNIQUE(FILTER(VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100),VSTACK(Sheet1!A2:A100,Sheet2!A2:A100,Sheet3!A2:A100)<>"")) and press Enter.
Frequently Asked Questions
Can UNIQUE and FILTER read arrays returned by VSTACK?
Yes. In spreadsheet apps that support dynamic arrays, VSTACK returns an array, and UNIQUE and FILTER can accept that array as input in one nested formula.
Why does my formula return #NAME? in Excel?
This usually means your Excel version does not support VSTACK, FILTER, or UNIQUE. Use Microsoft 365, or open the file in WPS Office Spreadsheets with dynamic array support.
How can I remove blank cells from a VSTACK result?
Wrap VSTACK in FILTER with a condition that checks the same VSTACK array, such as FILTER(VSTACK(...),VSTACK(...)<>"").
What should I check if I get a #VALUE! error?
Verify that all VSTACK ranges have the same number of columns and compatible data types, and confirm that the formula references match the actual source ranges exactly.
Does WPS Office support the VSTACK function?
Recent WPS Office Spreadsheets versions support dynamic array functions, including VSTACK, FILTER, and UNIQUE. Open your .xlsx file and enter the same formula.
