How to Create Unique IDs for Duplicate Values in Excel
Question details
The user wants to generate unique transaction IDs by adding sequential suffixes, such as -1, -2, and -3, to duplicate entries.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing transaction records, datasets, or inventory lists where duplicate identifiers exist and need to be transformed into unique distinct values for accurate tracking.
- Observed behavior
- Duplicate records currently share the exact same base ID, making it impossible to uniquely identify individual rows. The goal is to dynamically append an incrementing suffix based on how many times the value has appeared.
Before proceeding, make sure to save a backup copy of your workbook, especially if you plan to use VBA macros, as macro actions cannot be undone using the standard Undo shortcut.
Use the COUNTIF Formula to Add Sequential Suffixes
The most straightforward and widely supported method to create unique IDs is by using a dynamic expanding range within a COUNTIF formula.
This method combines the original ID with a hyphen and an incrementing count of its occurrence. The absolute row reference combined with a relative row reference creates an expanding range that automatically increments for duplicate entries.
Right-click the column header next to your existing IDs (for example, Column E next to Column D) and select 'Insert' to create space for your new unique IDs.
Select the first data cell in the new column (e.g., E2) and enter the following formula: =D2&"-"&COUNTIF(D$2:D2,D2)
Press Enter. Click the bottom-right corner of cell E2 (the fill handle) and drag it down to the bottom of your dataset to apply the unique suffixes.
Use MAP and LAMBDA Functions (Excel 365)
If you are using a modern version of Excel (Microsoft 365), you can utilize a single array formula that automatically spills down the entire column without needing to drag the fill handle.
Automate Unique ID Generation with a VBA Macro
If you frequently process data extracts with duplicate IDs, you can automate the process using a VBA macro to insert the column, apply the formula, and convert the results to values instantly.
Handle Duplicate Values Easily with WPS Spreadsheet
WPS Office Spreadsheet provides full support for advanced data manipulation, including dynamic COUNTIF ranges and array functions. You can seamlessly apply these unique ID generation methods within a clean, highly responsive interface.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the file containing your duplicate transaction IDs.
- 2. Apply the unique ID formula: In an adjacent blank column, enter the formula =D2&"-"&COUNTIF(D$2:D2,D2) in the first data row.
- 3. Drag and autofill: Use the fill handle at the bottom right of the cell to drag the formula down, instantly generating unique sequential suffixes.
- 4. Paste as values: Copy the entire newly generated column, right-click, and select 'Paste Special' > 'Values' to lock the IDs in place.
Frequently Asked Questions
Can I remove the original duplicate column after creating the unique IDs?
Yes, but you must first copy your new Unique ID column and choose 'Paste Special > Values'. If you delete the original column while the new column still contains formulas, the formulas will break and display reference errors.
Why is the COUNTIF formula returning an error?
Check your absolute and relative references carefully. The formula must be written with a locked start row (e.g., D$2) and a free end row (e.g., D2). Additionally, depending on your regional settings, you might need to use semicolons (;) instead of commas (,) in your formula.
Can I use letters as suffixes instead of numbers (e.g., -A, -B)?
Yes, you can modify the formula by wrapping the COUNTIF function in a CHAR function. For example, using =D2&"-"&CHAR(64+COUNTIF(D$2:D2,D2)) will return capital letters like -A, -B, and -C for each occurrence.
