How to Move Rows by Record Type to Separate Excel Worksheets
Question details
The user needs to distribute rows from a master source worksheet into individual destination worksheets automatically based on the record type.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing a large dataset containing mixed record types into separated tabs for easier data management without manual copy-and-paste operations.
- Observed behavior
- Currently, data is grouped in a single worksheet. The goal state is to have dynamic destination sheets that display only the rows corresponding to specific record types.
Ensure your master dataset has a dedicated column indicating the 'Record Type' and consider formatting your source data as a Table (Ctrl + T) to allow formulas to update automatically when new rows are added.
Use the Dynamic FILTER Function
This is the most efficient method for modern spreadsheet versions, allowing destination worksheets to update instantly when source data changes.
The FILTER function is a dynamic array formula that extracts records based on criteria without requiring repeated manual copy-pasting. It works seamlessly when separating data by specific text values.
Add a new blank worksheet for the first record type you wish to extract (e.g., 'PCODE').
Click on cell A1 in the new worksheet and enter the formula: =FILTER(Sheet1!A1:G11, Sheet1!A1:A11="PCODE", "No matching records"). Adjust the sheet name and ranges according to your source data.
If your data is formatted as a Table named 'TBL_Bouquets', enter this formula instead: =FILTER(TBL_Bouquets, TBL_Bouquets[Record Type]="PCODE", "No matching records"). Press Enter to populate the data dynamically.
Create additional worksheets for your other record types and repeat the formula, changing the criteria (e.g., "PCODE") to match the new type.
Use Power Query for Scheduled Updates
Power Query is an excellent alternative if you are using an older version of Excel that does not support dynamic arrays, or if you need to schedule data imports.
Use a VBA Macro for Bulk Processing
VBA macros are best suited for splitting a massive dataset containing dozens of record types instantly, saving you from setting up individual formulas.
Easily Filter and Separate Data with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, UNIQUE, and SORT. This allows you to effortlessly extract rows by record type into separate tabs in just a few clicks, keeping your data fully synced without manual copy-pasting.
- 1. Open your master dataset: Launch WPS Spreadsheet and open the file containing your mixed records.
- 2. Format as Table: Select your data and press 'Ctrl + T' to format it as a table. This makes your formulas robust.
- 3. Create a new tab: Click the '+' icon at the bottom sheet bar to create a destination worksheet.
- 4. Enter the FILTER formula: In cell A1 of the new sheet, type =FILTER(Table1, Table1[Record Type]="DesiredType") and press Enter to instantly pull the separated data.
Frequently Asked Questions
Does the FILTER function update automatically when I add new data?
Yes. If your source data is formatted as a Table (or if you reference entire columns), any new rows you add to the source dataset will automatically be filtered and appear in the corresponding destination worksheet.
What happens if there are no records matching the specified type?
You can provide a fallback message by utilizing the third argument of the FILTER function. For example: =FILTER(A1:G11, A1:A11="PCODE", "No records found"). If no match exists, the cell will display 'No records found' instead of a #CALC! error.
Will dynamic array formulas like FILTER work in older versions of Excel?
No, the FILTER function requires versions that support dynamic arrays, such as Microsoft 365, Excel 2021, or the latest WPS Office. For older software, you should use the Power Query method or a VBA macro.
Can I filter data based on multiple criteria at once?
Yes. You can multiply criteria conditions together inside the FILTER function to achieve an 'AND' logic. For example: =FILTER(A1:G11, (A1:A11="PCODE")*(B1:B11="Active")) will return rows that match both conditions.
