Match Project Names Between Tables in WPS Office Without Breaking Sort Order
Question details
The user needs to populate one table from another based on matching project names, using a method that remains correct when the source table is sorted and supports multiple role columns.
- Product
- WPS Office Spreadsheets (Power Query)
- Device & OS
- not provided
- Scenario
- The user has two tables containing project names and several role columns such as PM, APM, MAPA, and CM1 through CM5, and needs to populate one table from the other based on matching project names.
- Observed behavior
- Fixed cell references return incorrect values when the source table is sorted, so a relational query method is required.
Check that both tables have a shared project-name column with consistent spelling, no extra spaces, and no unintended duplicate names. Make sure your WPS Office version includes Power Query under the Data tab.
Use Power Query to merge tables by project name and unpivot role columns
Use this method when you need the matched role columns to remain correct even after sorting, filtering, or refreshing the source data.
Power Query creates a relationship based on the project-name column instead of relying on cell addresses. Merging the queries matches each project once, and unpivoting lets you handle several role columns such as PM, APM, MAPA, and CM1 through CM5 in a single step.
Select a cell inside the first table, click Data > Get Data > From Table/Range, then click OK. Repeat this for the second table.
In the Power Query Editor, click Home > Merge Queries, choose both tables, select the project-name column in each table, and click OK.
Click the expand icon in the new column, select PM, APM, MAPA, and CM1 through CM5, and confirm to add them to the query.
Select the project-name column, click Transform > Unpivot Other Columns so the role fields become rows that can be filtered or pivoted as needed.
Click Home > Close & Load To > Table and choose where to place the final matched table.
Match Project Names Between Tables in WPS Office Without Breaking Sort Order
Use WPS Office Power Query to merge tables by project name and unpivot PM, APM, MAPA, and CM role columns. The query refreshes correctly after sorting because it uses column relationships instead of fixed cell references.
- 1. Open Power Query in WPS Office: In WPS Office Spreadsheets, click Data > Get Data > From Table/Range to create queries for both project tables.
- 2. Merge by project name: Click Home > Merge Queries in the Power Query Editor, select both queries, click the matching project-name columns, and click OK.
- 3. Unpivot role columns: Select the project-name column, click Transform > Unpivot Other Columns to convert PM, APM, MAPA, and CM1-CM5 columns into rows.
- 4. Load the sort-safe table: Click Home > Close & Load To > Table to place the matched result in the workbook.
Frequently Asked Questions
Why do fixed cell references return wrong values after sorting?
Fixed references point to cell positions, so sorting changes which record is in that position. Power Query merges by the project-name column value instead, so the match follows the project name.
Do both tables need the same project-name column header?
No. Power Query Merge Queries lets you select the matching column from each table independently. The column names can be different, but the values should be consistently formatted.
Can WPS Power Query handle PM, APM, MAPA, and CM1-CM5 role columns?
Yes. Expand the role columns after merging, then use Unpivot Other Columns to turn the multiple role columns into a long format that is easy to filter, pivot, or audit.
How do I update the matched table when the source data changes?
Refresh the query from the Data tab or right-click the output table and choose Refresh. WPS Office Power Query re-runs the merge based on project names.
What should I do if project names are duplicated in the source table?
Remove duplicates in Power Query before merging, or create a unique project-name key. Merging on duplicate keys can multiply rows and make result totals look incorrect.
