Function Problems

How to Create Sequential Unique IDs in Excel While Skipping Duplicates

Kushani Nimanthika Oct 22, 2026 868 views

Question details

The user needs to generate custom sequential IDs (e.g., REP01, REP02) for a list of items, ensuring that repeated entries receive the same ID as their first occurrence without increasing the sequence count.

How to Create Sequential Unique IDs in Excel While Skipping Duplicates

Product: Excel
Device & OS: not provided
Scenario: Organizing and assigning unique identification codes to a dataset that contains duplicate entries.
Observed behavior: Custom sequential IDs are assigned accurately to unique items, and duplicates inherit the previously assigned ID without disrupting the sequence.

Before you start

Ensure that your data is organized in a contiguous column without empty rows, and verify that your version of Excel or WPS Office supports dynamic array functions like UNIQUE.

Solution 1Recommended

Use a Combination Formula with COUNTIF, MATCH, and UNIQUE

Apply a formula that intelligently checks for duplicates, identifies their original position, and counts only distinct items to generate your unique IDs.

This method uses the COUNTIF function to detect duplicates, MATCH to locate the first occurrence of a repeated name, and UNIQUE to count only distinct names.

The TEXT function formats the numerical count into a specific string length (like '01' instead of '1'), and the ampersand (&) concatenates it with your chosen text prefix.

Prepare your data

Assume your list of names begins in cell B2. Click on the first cell in the adjacent column where you want the ID to appear (e.g., cell A2).

Enter the formula

Type the following formula exactly: ="REP"&TEXT(IF(COUNTIF(B$2:B2,B2)>1,MATCH(B2,$B$2:$B$13,0),COUNTA(UNIQUE(B$2:B2))),"00")

Adjust ranges

Modify the range $B$2:$B$13 in the MATCH function to perfectly match the actual total range of your dataset to prevent errors.

Fill down

Press Enter to apply the formula, then click and drag the fill handle (the small square at the bottom-right of the cell) down to apply the formula to the rest of the column.

Use a Combination Formula with COUNTIF, MATCH, and UNIQUE

Formula Breakdown: The UNIQUE function is vital here; it ignores repeated values and ensures the counter only goes up when a genuinely new name appears in the list.

Efficient Spreadsheet Management

Create Unique Sequential IDs Easily in WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions like UNIQUE, making it straightforward to manage complex data manipulation, including generating skip-duplicate sequential IDs seamlessly.

1. Open your file in WPS: Launch WPS Spreadsheet and open your dataset containing the names you want to assign IDs to.
2. Apply the custom formula: Select the target cell and input the exact formula: ="REP"&TEXT(IF(COUNTIF(B$2:B2,B2)>1,MATCH(B2,$B$2:$B$13,0),COUNTA(UNIQUE(B$2:B2))),"00")
3. Drag to fill: Use the fill handle to drag the formula down the column to automatically assign sequential unique IDs to all items in your dataset.

Full compatibility with Microsoft Excel formulas, including UNIQUE, COUNTIF, and MATCH.Lightweight application that processes heavy spreadsheet data quickly without freezing.Free built-in data processing and analysis tools.Familiar user interface makes it easy to transition from other spreadsheet software.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #NAME? error when using the UNIQUE function?

The #NAME? error usually occurs if you are using an older version of Excel that does not support dynamic array functions like UNIQUE. You will need to upgrade to a newer version like Office 365, Excel 2021, or use a compatible alternative like WPS Office.

How can I change the prefix of the ID from 'REP' to something else?

In the formula, simply replace the text inside the first set of quotation marks. For example, to use 'EMP', change the beginning of the formula to ="EMP"&TEXT(...).

Can I make the sequential numbers three digits instead of two?

Yes. Within the TEXT function of the formula, change the format code from "00" to "000". The formula will then output IDs with three digits, such as REP001, REP002.

What if my data starts in a different row or column?

You must adjust the cell references in the formula. For example, if your names start in cell C5 instead of B2, change B$2:B2 to C$5:C5 and adjust the absolute range $B$2:$B$13 to match your new data column's full range.

Excel & Data Problems