VBA & Macro Problems

How to Add New Orders to an Excel List Without Duplicates Using VBA

Rana GarciaRana Garcia Nov 10, 2026 869 views

Question details

The user wants to update an order list by copying marked items from a catalog while avoiding duplicate entries.

How to Add New Orders to an Excel List Without Duplicates
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press ALT + F11 to open the VBA Editor, then go to Insert > Module to create a new blank script.

2
Set up the loop

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').

3
Check for duplicates

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.

4
Append missing records

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.

5
Sort the updated 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.

Automate Order Updates with a VBA Macro
Tip: Use Excel Tables: Converting your destination list to an official Excel Table (Ctrl + T) ensures your VBA macro can easily find the last row and automatically apply formatting to new entries.
Advanced Spreadsheets

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. 1. Open your workbook: Launch WPS Spreadsheet and open your existing catalog and order list workbook.
  2. 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. 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. 4. Run the automation: Run the macro to instantly update your order list, avoiding duplicates and keeping your data perfectly sorted.
Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Robust built-in VBA/Macro support for automating complex workflows.Advanced data validation and conditional formatting tools to spot duplicates instantly.Free, lightweight, and incredibly easy to use.
microsoft office alternative - wps office

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.