Formula Errors

How to Create a Unique Reference Number from an ID and Count in Excel

John Wilson Oct 15, 2026 869 views

Question details

The user needs to generate unique reference numbers by appending an incrementing occurrence count to duplicate IDs in a spreadsheet.

How to Create a Unique Reference Number from an ID and Count in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing database records or inventory where items share a base ID but require unique identifiers for tracking purposes.
Observed behavior: Repeated IDs must automatically receive an incrementing sequence number (e.g., A1, A2, A3) as the formula is filled down the column.

Before you start

Ensure your data is organized in columns with a header row, and identify the specific column containing the base IDs you want to use for generating unique references.

Solution 1Recommended

Use the COUNTIF Function Combined with the Base ID

This method uses a mixed cell reference in the COUNTIF function to dynamically count occurrences of an ID as you drag the formula down the column.

By combining the cell value with a dynamic count, you can append a unique sequence number to every instance of an ID.

Locate your base ID

Identify the first cell containing the ID you want to number. For this example, assume your first ID is located in cell B2.

Enter the combining formula

Select an adjacent empty cell in the same row (e.g., C2) and enter the following formula: =B2&COUNTIF(B$2:B2,B2).

Fill the formula down the column

Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply it to the rest of your data.

Use the COUNTIF Function Combined with the Base ID

How the formula works: The B$2:B2 range locks the starting row but leaves the ending row relative. As you copy it down, it counts exactly how many times the specific ID has appeared up to that current row.

Solution 2

Add a Separator Between the ID and Sequence Number

If you want the unique reference number to be more readable, you can insert a character like a hyphen or underscore between the ID and the sequence number.

Effortless Data Management

Generate Unique Reference Numbers Easily in WPS Spreadsheet

WPS Office provides a powerful, highly compatible spreadsheet tool that seamlessly handles advanced formulas like COUNTIF. You can quickly generate unique IDs and manage large datasets with zero friction.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the base IDs.
2. Input the dynamic formula: Select an empty cell next to your first ID and type =B2&COUNTIF(B$2:B2, B2).
3. Auto-fill the column: Double-click the fill handle in the bottom-right corner of the cell to instantly apply the unique reference numbers to your entire list.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx).Lightweight and fast, even when processing thousands of rows of data.Free to use with a familiar, user-friendly tabbed interface.

Frequently Asked Questions

Why does my sequence number not reset for different IDs?

Ensure the criteria in your COUNTIF function correctly references the specific ID in the current row (the second argument should be B2, not a hardcoded text string). The sequence will naturally reset to 1 when the formula encounters a new ID it hasn't counted before.

Can I add leading zeros to the count (e.g., A01 instead of A1)?

Yes, you can use the TEXT function to format the count. Modify your formula to: =B2&TEXT(COUNTIF(B$2:B2, B2), "00"). This forces the count to display as two digits.

What if my data doesn't start in row 2?

Adjust the row numbers in the formula to match your actual starting row. If your first ID is in B5, the formula would be =B5&COUNTIF(B$5:B5, B5).

Will sorting the data break the sequence numbers?

Because the formula relies on a dynamic expanding range (B$2:B2), sorting the rows may change the occurrence count for specific items based on their new position. To make the unique references permanent before sorting, copy the formula column, right-click, and select 'Paste as Values'.

Excel & Data Problems