How to Replace Temporary IDs with Correct IDs in Excel Data Using WPS Office VBA
Question details
The user needs to update every matching temporary identifier for a specific pool or recording in a large Excel-compatible dataset while preserving accuracy and auditability.
- Product
- WPS Office Spreadsheets (Excel-compatible)
- Device & OS
- not provided
- Scenario
- The user has a large dataset containing temporary IDs and needs to replace them with corrected IDs for a selected pool or recording without affecting unrelated rows.
- Observed behavior
- Manual replacement risks errors and makes it hard to verify which rows changed, so the user needs a controlled method that keeps the original ID for audit.
Before making changes, confirm whether your data uses Pool ID in column A and Temporary ID in column D as in the example, and save a backup copy of the workbook or sheet so you can restore it if the replacement affects the wrong rows.
Replace Temporary IDs with a VBA Macro in WPS Office Spreadsheets
Use this method when you need a controlled, repeatable replacement that updates every matching row for a selected pool and temporary ID.
The macro reads the pool and temporary ID from the active row, prompts you for the corrected ID, and updates only rows where both criteria match. This keeps the replacement scoped to the intended pool and recording.
In WPS Office Spreadsheets, click the Developer tab, then click Macros or press Alt+F11 to open the Microsoft Visual Basic for Applications editor. If the Developer tab is not visible, right-click the ribbon, choose Customize Ribbon, and enable Developer.
In the VBA editor, click Insert > Module to create a new module where the replacement macro will run.
Paste a macro that reads Pool ID from column A and Temporary ID from column D on the active row, prompts for the corrected ID with an InputBox, loops from row 2 to the last row, and updates column D only for rows where both values match.
Return to WPS Spreadsheets, select a cell on the row with the pool and temporary ID you want to correct, open the Macros dialog, choose the replacement macro, and click Run.
Before you run the macro, copy the original temporary ID column into an Audit Temporary ID column so you can verify which values changed.
Use a Lookup Table to Add Corrected IDs in an Audit-Friendly Way
Choose this formula-based approach when you want to preserve the original ID and display the corrected ID without overwriting existing data.
Replace Temporary IDs in WPS Office Spreadsheets Without Losing Audit History
Use WPS Office Spreadsheets to safely replace temporary IDs in Excel-compatible workbooks with a VBA macro or lookup table. The macro updates only the pool and temporary ID you confirm, while an audit column keeps the original ID for verification.
- 1. Open your Excel workbook: Launch WPS Office Spreadsheets and open the .xlsx or .xlsm workbook that contains the temporary IDs.
- 2. Enable Developer and add an audit column: Enable the Developer tab from the ribbon, then insert an Original Temporary ID column and copy the current IDs into it.
- 3. Insert and run the VBA macro: Open the VBA editor from the Developer tab, insert a module, paste the lookup-and-replace macro, and run it from the active data row.
- 4. Verify the replacement: Filter the sheet by pool or temporary ID, compare the updated column with the audit column, and check the reported changed-row count.
Frequently Asked Questions
Can I replace temporary IDs in WPS Office without using VBA?
Yes. Create a lookup table with Temporary ID and Corrected ID columns, then use VLOOKUP or XLOOKUP in a new column to display corrected IDs. This does not physically replace the original values unless you paste them as values.
How do I keep the original temporary ID for auditing?
Before running any replacement, insert a new column such as Original Temporary ID and copy the current ID values into it. That column remains untouched for comparison.
How do I limit the replacement to one pool and one temporary ID?
In the VBA approach, select a cell on the row with the intended pool and temporary ID. The macro reads column A and column D from that active row and updates only rows where both match.
Does the VBA macro work with Microsoft Excel files in WPS Office?
Yes. WPS Office Spreadsheets supports Excel-compatible formats including .xlsx and .xlsm. If macros are stored in the workbook, save it as a macro-enabled file.
What should I do if the macro replaces the wrong rows?
Close the workbook without saving or restore it from the backup you made before running the macro, then verify the active row criteria and the mapping before running it again.
