How to Add New Orders to an Excel List Without Duplicates Using VBA
Question details
The user wants to update an order list by copying marked items from a catalog while avoiding duplicate entries.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing and updating an order list dynamically from a product catalog without manually filtering out existing orders.
- Observed behavior
- Standard copy-pasting overwrites rows or creates duplicate order entries. The user needs a seamless way to append only missing records and sort them by an internal reference.
Ensure that your internal reference or order ID column is formatted consistently across both lists (e.g., all text or all numbers) to prevent false mismatches. Save a backup of your workbook before testing any new VBA macros.
Automate Order Updates with a VBA Macro
A VBA script can scan your catalog for marked items, check the order list for existing reference IDs, and append only the new records.
Using a VBA macro is the most efficient way to handle repetitive data entry without manually checking for duplicates. The macro will use a unique key, such as an internal reference number, to verify if the order already exists in your destination list before copying it over.
Press ALT + F11 to open the VBA Editor, then go to Insert > Module to create a new blank script.
Write a macro that loops through the catalog range and checks if the specific item row is marked for order (for example, checking if column A contains an 'X').
Inside the loop, use the WorksheetFunction.CountIf method to check if the internal reference ID from the marked catalog row already exists in your destination list's ID column.
If the CountIf result is zero, copy the row's data and append it to the first empty row at the bottom of your destination list.
Add a sorting function at the end of the script using the Range.Sort method to automatically sort the newly updated destination list by the internal reference column.

Build a VBA UserForm for Safer Order Entry
A custom UserForm allows you to input orders directly, verifying duplicates before data is ever written to the worksheet.
Use WPS Spreadsheet to Manage Lists and Macros
WPS Spreadsheet fully supports VBA macros, making it easy to automate order lists, prevent duplicates, and manage large product catalogs efficiently.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing catalog and order list workbook.
- 2. Access the Developer tab: Go to the 'Developer' tab on the top ribbon and click 'Macros' or 'Visual Basic' to access the code editor.
- 3. Add your macro: Paste or write your macro code designed to filter marked catalog items and prevent duplicates using COUNTIF or Dictionary objects.
- 4. Run the automation: Run the macro to instantly update your order list, avoiding duplicates and keeping your data perfectly sorted.

Frequently Asked Questions
Can I prevent duplicates when copying lists without using VBA?
Yes, you can use the built-in 'Remove Duplicates' feature found on the Data tab. Simply copy and paste all marked items to the bottom of your order list, highlight the entire list, click 'Remove Duplicates', and select your internal reference ID column as the criteria.
Why is my VBA macro still adding duplicate orders?
This usually happens if the data types of the internal reference IDs don't match (for example, numbers stored as text in the catalog, but stored as numbers in the order list). Ensure both columns share the exact same formatting.
How do I sort my list automatically after adding new orders?
You can record a macro while manually sorting the list by your reference column. Stop recording, copy the generated sorting code from the new module, and paste it at the very end of your existing order-updating macro.
Does the XMATCH function work in older versions of Excel?
No, XMATCH is only available in Microsoft 365 and Excel 2021 or newer. If you need your macro to be compatible with older versions, rely on the MATCH or COUNTIF functions within your VBA code.




