Function Problems

How to Create Unique Excel IDs with Sequential Duplicate Suffixes

Olivia Miller Sep 28, 2026 871 views

Question details

Generate unique employee IDs by combining staff initials and the last four digits of their staff ID, automatically appending a sequential numerical suffix (e.g., -01, -02) for any duplicates.

How to Create Unique Employee IDs with Sequential Suffixes in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an employee roster or database where identifiers must remain absolutely unique, even if the base criteria (initials and partial ID) happen to overlap with another employee.
Observed behavior: Need a reliable formula structure to extract specific text characters, combine them into a base identifier, count previous occurrences of that identifier, and append a formatted numerical suffix.

Before you start

Ensure your source data columns (such as First Name, Last Name, and Staff ID) are well-organized and do not contain accidental leading or trailing spaces before applying text extraction formulas.

Solution 1Recommended

Use a Helper Column with the COUNTIF Function

This two-step method is highly recommended as it keeps the formulas simple, easier to read, and less prone to calculation errors.

By first generating a base identifier and then counting its occurrences, you can easily append a formatted suffix for duplicates.

Create the base identifier

In a separate helper column (e.g., Column A), enter the formula to combine the initials and last four digits. Assuming First Name is in B2, Last Name in C2, and Staff ID in D2, use: =LEFT(B2,1)&LEFT(C2,1)&RIGHT(D2,4)

Create the complete ID with sequential suffix

In the target column for the final ID, reference the helper cell and count occurrences using this formula: =A2&"-"&TEXT(COUNTIF($A$2:A2,A2),"00"). This adds -01, -02, etc., based on how many times the base ID has appeared so far.

Copy formulas and convert to values

Drag the fill handle to copy both formulas down your data range. If you plan to sort the data later or need the IDs to remain fixed, select the final IDs, copy them, and choose "Paste as Values" to remove the underlying formulas.

Use a Helper Column with the COUNTIF Function

Absolute and Relative Referencing: Notice the use of $A$2:A2 in the COUNTIF formula. Locking the first row ensures the counting range expands correctly as you drag the formula down the column.

Solution 2

Use a Single Advanced SUMPRODUCT Formula

Use this method if you prefer not to use any helper columns and want to generate the final ID entirely in one step.

Advanced Formulas in WPS Office

Generate Unique IDs Seamlessly in WPS Spreadsheet

WPS Spreadsheet fully supports advanced text operations and counting formulas like LEFT, RIGHT, COUNTIF, and SUMPRODUCT, helping you manage complex employee databases quickly and efficiently.

1. Open your data file: Launch WPS Spreadsheet and open the employee database where you need to generate IDs.
2. Enter the base formula: Create a new column and type your text combination formula using LEFT and RIGHT functions.
3. Append the duplicate counter: Add the sequential duplicate suffix using the COUNTIF formula with an expanding range.
4. Apply and fix values: Drag the fill handle down to populate the list, then copy and paste the results as values to make the IDs permanent.

Fully compatible with Microsoft Excel formulas and functionsFree, lightweight, and fast spreadsheet toolIntuitive interface for handling complex data arraysSeamlessly transition your existing Excel files without formatting loss

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't my sequential suffix number increase when I copy the formula down?

This usually happens because the range in your COUNTIF or SUMPRODUCT formula is not anchored correctly. Ensure you are using absolute references for the start of your range and relative references for the end (e.g., $A$2:A2). This allows the range to expand dynamically as it moves down the rows.

How do I make the generated IDs permanent so they don't change when I sort the data?

Formulas recalculate dynamically based on cell positions. To fix the IDs permanently, highlight the column containing the final IDs, copy the cells (Ctrl+C), right-click the same selection, and choose 'Paste as Values'. This converts the formulas into static text.

Can I change the suffix to display three digits instead of two?

Yes. In the formula, locate the TEXT function and change the format argument from "00" to "000". For example, change TEXT(COUNTIF($A$2:A2,A2),"00") to TEXT(COUNTIF($A$2:A2,A2),"000"). This will generate suffixes like -001, -002, etc.

What happens if a cell referenced in the LEFT or RIGHT formula is blank?

If a referenced cell is empty, the LEFT or RIGHT function will simply return nothing for that part of the string. To avoid malformed base IDs, ensure all necessary data fields (like First Name, Last Name, and Staff ID) are fully populated before running the formula.

Excel & Data Problems