How to Move Rows for Selected ZIP Codes to Another Excel Sheet in WPS Office
Question details
The user needs to extract rows from a large Excel dataset that match a separate list of selected ZIP codes and place them into another sheet without searching for each ZIP code individually.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working with a large names-and-addresses dataset and a ZIP-code list of about 200 values; the goal is to isolate matching rows automatically instead of checking each ZIP code manually.
- Observed behavior
- Manual searching is repetitive and slow, so the user wants a query-based merge that moves matching rows and can be refreshed when the source data changes.
Before creating the query, format both the source data and the ZIP-code list as Excel tables. Make sure the ZIP-code column has the same header name in both tables so Power Query can match them reliably.
Use Power Query to Merge the ZIP-Code List and Load Matching Rows
Best for large and changing datasets because the query is refreshable and does not require searching for each ZIP code individually.
Power Query performs an inner join between the source table and the ZIP-code list. It keeps only source rows whose ZIP code appears in the list, and the loaded results can be refreshed whenever either table changes.
Select a cell in the source range, press Ctrl+T, and confirm the table range. Repeat for the ZIP-code list, and use the same ZIP-code column header in both tables.
Click any cell in the source table, go to the Data tab, and choose From Table/Range. Repeat this for the ZIP-code table so both tables are available as queries for the merge.
In the Power Query Editor, go to Home > Merge Queries > Merge Queries as New. Select the source table and the ZIP-code table, click the ZIP-code column in each table, and choose Join Kind > Inner.
In the merged query, right-click the unnecessary ZIP-list column headers and choose Remove Columns so only the desired source fields remain.
Go to Home > Close & Load To, select New worksheet, and click Load. After source changes, click Data > Refresh All to update the matching rows.
Use Advanced Filter to Copy Matching ZIP-Code Rows to Another Sheet
A good non-Power Query option when you need a one-time copy or prefer to work directly on the sheet.
Move Selected ZIP-Code Rows to Another Sheet with WPS Office Power Query
Use WPS Office Spreadsheets to set up both data ranges as Excel tables, merge them by ZIP code in Power Query, and load only matching rows to a new worksheet. The query refreshes with source changes, so you do not redo manual searches.
- 1. Format your data: Open the workbook in WPS Office Spreadsheets, convert the source data and ZIP-code list into Excel tables with matching ZIP-code column headers.
- 2. Open Power Query: Go to the Data tab and use From Table/Range for both tables so they are available in the Power Query Editor.
- 3. Merge by ZIP code: Select Home > Merge Queries as New, choose the source table and ZIP-code table, click both ZIP-code columns, and use an Inner join.
- 4. Load to a new sheet: Remove unwanted columns if needed, then choose Home > Close & Load To and load the merged query into a new worksheet.
- 5. Refresh when data changes: After updating source rows or ZIP codes, click Data > Refresh All to update the new sheet automatically.
Frequently Asked Questions
Can I move rows for 200 ZIP codes without typing each ZIP code into a filter?
Yes. Use either Power Query merge or Advanced Filter with a criteria range. Both methods compare the entire source dataset against the ZIP-code list at once.
How do I refresh the moved rows when I add new source data?
In the Power Query solution, update the source table or ZIP-code table and go to Data > Refresh All. The matching rows in the destination worksheet will refresh automatically.
Does this method work when opening the workbook in WPS Office and Microsoft Excel?
Yes. WPS Office opens and saves .xlsx and .xls Excel files, and Power Query queries created in the workbook can be refreshed in compatible Excel environments.
What if my ZIP-code column headers are different?
You can still map them in the Merge Queries dialog by selecting the correct columns. For Advanced Filter, the criteria range header must match the source data header exactly.
Can I choose which columns appear in the new sheet?
Yes. In Power Query, remove or reorder columns before using Close & Load To. With Advanced Filter, arrange only the destination columns you want to copy.
