How to Find Duplicate Numbers and Assign Them a Serial Number in Excel

Managing data efficiently is a critical skill, especially when working with large datasets in Excel. One common challenge is identifying duplicate numbers and organizing them systematically. Duplicates can clutter your data and hinder accurate analysis, making it essential to pinpoint and address them. This guide demonstrates a straightforward method to find duplicate numbers and assign them a serial number, helping to keep your data structured and clear. Using Excel's built-in formulas, such as COUNTIF, and simple logic, you can automate this task efficiently. Whether you're working with sales figures, inventory lists, or any numerical data, this approach ensures precision and saves time.

To identify duplicate numbers in Excel and assign them a serial number, follow these steps:

Step 1: Identify Duplicate Numbers

1. Open your Excel file containing the list of numbers.
2. Assume your data is in Column A, starting from cell A1.
3. In Column B, use a formula to identify duplicates. Enter the following formula in cell B2 (adjust the range as needed):

=IF(COUNTIF(A$2:A2, A2) > 1, "Duplicate", "Unique")

This formula checks if a number appears more than once up to the current row. Drag the formula down to the end of your list.

Step 2: Assign Serial Numbers to Duplicates 

• In Column C, enter the following formula in cell C2 to assign a serial number to the duplicates:

=IF(B2="Duplicate", COUNTIF(A$2:A2, A2)-1, "")

This formula calculates the occurrence number of each duplicate and assigns it a serial number. The -1 ensures the first occurrence is not counted as a duplicate.

• Drag the formula down the column to process all rows.

Example

Step 3: Customize (Optional)
If you only want to show serial numbers without "Duplicate/Unique" labels, you can skip Column B and directly use this formula in Column C:

=IF(COUNTIF(A$2:A2, A2) > 1, COUNTIF(A$2:A2, A2)-1, "")

This method works for dynamic datasets and automatically updates when values are changed.