How to Separate Excel Data onto Worksheets by Account Number in WPS Office
Question details
The user needs to automatically display rows from Excel source sheets on different destination worksheets based on a natural account number stored in each record.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A workbook contains combined records from one or more source sheets, and each row includes an account number. The user wants each account's rows isolated onto its own worksheet without manual copying and pasting.
- Observed behavior
- The destination sheets should automatically list only the rows whose account number matches the account number placed in that sheet, and they should update when source data changes.
Make sure your source data uses a consistent range and that the account number column is in the same position across all source sheets. Create a destination sheet for each account number and put the target account number in cell A1 on that sheet.
Split Data Dynamically with FILTER and VSTACK Formulas in WPS Office Spreadsheets
Use this method when you want the destination worksheets to update automatically whenever source data changes.
This formula method works best when each destination sheet has a reference cell, such as A1, containing the account number to display. VSTACK combines matching rows from multiple source sheets into one spill range, and FILTER selects only the rows whose account column equals that cell.
Open your workbook in WPS Office Spreadsheets. On each destination worksheet, type the target account number into cell A1 and keep that sheet selected.
In the destination sheet, select the top-left cell where you want the results to start. Type =VSTACK(FILTER(Sheet1!A1:Z10000,Sheet1!A1:A10000=A1),FILTER(Sheet2!A1:Z10000,Sheet2!A1:A10000=A1),FILTER(Sheet3!A1:Z10000,Sheet3!A1:A10000=A1)) and press Enter.
Replace Sheet1, Sheet2, Sheet3 and the ranges with your actual source sheet names and data ranges. Confirm that the account number column is the first argument in each FILTER range and the condition range matches the account column.
Change a source row's account number or add matching rows inside the referenced ranges. The destination sheet recalculates and displays only the rows matching the account number in cell A1.
Create One Worksheet per Account with a PivotTable Report Filter
Use this no-formula method if you prefer WPS Spreadsheets' built-in reporting tools to generate separate account worksheets.
Use WPS Office Spreadsheets to Separate Excel Data by Account Number
WPS Office Spreadsheets can open your existing Excel workbook and let you split rows into individual account worksheets using dynamic VSTACK and FILTER formulas, so each destination sheet updates automatically when the source data changes.
- 1. Open the workbook in WPS Office Spreadsheets: Download and install WPS Office, then open the Excel workbook that contains the combined source data.
- 2. Prepare destination sheets: Create a worksheet for each account number and enter the target account number in cell A1 on each sheet.
- 3. Enter the dynamic formula: On a destination sheet, select the top-left result cell and enter the VSTACK and FILTER formula that references your source sheets and cell A1, then press Enter.
- 4. Verify the account rows: Check that WPS Spreadsheets displays only the matching account rows, and edit or add source data inside the referenced ranges to confirm the sheet updates automatically.
Frequently Asked Questions
Which spreadsheet versions support VSTACK and FILTER for this method?
The formula method works in WPS Office Spreadsheets and Microsoft 365/Excel 365-compatible versions. In older Excel versions without dynamic arrays, use the PivotTable Show Report Filter Pages method or manual filtering instead.
Why does my formula return a #CALC! error?
This usually means no rows match the account number in cell A1. Check the account number for spelling or extra spaces, make sure the account column is included in the FILTER range, and verify that the destination cell A1 contains exactly the target value.
Can I split data from more than three source sheets?
Yes. Add one more FILTER range, such as FILTER(Sheet4!A1:Z10000,Sheet4!A1:A10000=A1), to the VSTACK formula for each additional source sheet, separated by commas.
Will changes in the source sheet automatically appear on the destination sheet?
Yes, when you use VSTACK and FILTER formulas, WPS Office Spreadsheets recalculates the destination range and displays the matching rows automatically when source values within the referenced ranges change or new matching rows are added.
Is there a no-formula way to create one worksheet per account?
Yes. Insert a PivotTable, drag Account Number to the Filters area, then use Show Report Filter Pages under the PivotTable Analyze tab to generate separate account worksheets.
