How to Create Criteria-Based Excel Tables in WPS Office with VBA or Dynamic Arrays
Question details
The user wants to generate separate output tables for each criterion listed on another worksheet, using VBA or dynamic array formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A source range contains values with associated criteria, and a separate criteria list specifies which values should appear in each corresponding output table.
- Observed behavior
- An output table is created for each criterion such as A or F, and previous output is replaced to avoid stale values.
Make sure your source range and criteria list use consistent headers, and check your WPS Office version for FILTER function support before choosing between a formula-based or VBA solution.
Create Separate Output Tables with the FILTER Dynamic Array Formula
Use this method when your WPS Office version supports dynamic arrays and you want a formula-driven solution that updates automatically.
The FILTER function returns all rows from a source range that match a criterion. Because the results spill into adjacent cells, each output table can be built by referencing one criterion cell on the criteria sheet.
In WPS Spreadsheets, open your workbook, select the source values range, click the Name Box, and name it SourceValues; then select the source criteria range and name it SourceCriteria.
Select the first output cell on the target sheet, type =FILTER(SourceValues,SourceCriteria=Sheet2!A2,""), and press Enter to spill the matching rows.
Drag or copy the formula to the next output table start cell, then update the criterion reference from Sheet2!A2 to Sheet2!A3, Sheet2!A4, and so on.
Select each spilled range and apply table formatting, headers, borders, or a named Excel table as needed for the final output.
Build Criteria-Based Output Tables with a VBA Macro Loop
Use this method when you need a repeatable automated process, many criteria, or compatibility with WPS Office versions that do not support the FILTER function.
Create Criteria-Based Output Tables in WPS Office Spreadsheets
WPS Office Spreadsheets can split source data into separate criteria-based tables using Excel-compatible FILTER formulas or a reusable VBA macro. Both methods work within a familiar spreadsheet interface and preserve your .xlsx or .xlsm files without conversion.
- 1. Load the workbook: Open WPS Office Spreadsheets and load the workbook that contains your source data and criteria sheet.
- 2. Save as a macro-enabled workbook if needed: If you plan to use VBA, click Menu > Save As and choose .xlsm format so macros are retained.
- 3. Apply the criteria-based method: Use the FILTER formula on the output sheet or run the VBA macro from the developer tools to generate each criterion's table.
- 4. Review and format the output: Check each generated table, adjust formatting or naming, and save the workbook for reuse.
Frequently Asked Questions
Does WPS Office support the Excel FILTER function for dynamic arrays?
Recent versions of WPS Office Spreadsheets support FILTER and other dynamic array functions. If FILTER is not available in your version, use the VBA macro loop method instead.
How do I avoid stale values when rebuilding criteria-based tables?
With a formula solution, spilled results update automatically. With VBA, clear previous output sheets or ranges at the beginning of the macro before copying new filtered rows.
Can each criterion create a table on its own sheet?
Yes. A VBA macro can loop through the criteria list, create or rename a sheet for each criterion, filter the source data by that criterion, and copy the visible rows into the corresponding sheet.
What should I do if my criteria list changes frequently?
If you use FILTER formulas, the output tables update immediately when criteria or source data change. If you use VBA, rerun the macro after changing the criteria list so the output is rebuilt.
Is it safe to run VBA macros in WPS Office?
Only run macros from trusted workbooks. In WPS Spreadsheets, you can enable or disable macros in the security settings and review the code before running it.
