Formula Errors

How to Create Sequential Numbers That Reset When a Name Changes in Excel

WPS Content Manager Sep 28, 2026 869 views

Question details

The user needs to generate row numbers that start at 1 and increment sequentially for each grouped name, resetting back to 1 whenever the name in the reference column changes.

How to Create Sequential Numbers That Reset When a Name Changes in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing and numbering grouped data within a large dataset of approximately 83,000 rows.
Observed behavior: Sequential numbers need to increment within the same group and automatically reset to 1 when a new group name is encountered.

Before you start

Ensure your data is sorted by the column containing the names before applying these formulas, so identical names are grouped together continuously.

Solution 1Recommended

Use the IF Formula for Sorted Groups

This is the most efficient method for large datasets where identical names are grouped together, using a simple logical check.

The IF function calculates extremely fast, making it the ideal choice for processing worksheets with tens of thousands of rows without causing performance issues or lagging.

Locate your starting cells

Assuming your list of names is in column A starting from cell A2, click on cell B2 where you want the first sequence number to appear.

Enter the IF formula

Type =IF(A2=A1,B1+1,1) into cell B2 and press Enter. This formula checks if the current name matches the one directly above it. If it matches, it adds 1 to the previous number; if it doesn't match, it resets the sequence to 1.

Apply the formula to all rows

Select cell B2, then double-click the small square at the bottom-right corner of the cell (the fill handle) to automatically copy the formula down through all 83,000 rows.

Fast Calculation: Because this formula only looks at the cell immediately above it, it executes almost instantly even on massive datasets.

Solution 2

Use the COUNTIF Formula for Unsorted Occurrences

Use this alternative method if you need to count every occurrence of a name throughout the entire list, regardless of whether the identical names are grouped together consecutively.

Process Large Datasets Easily

Seamlessly Generate Sequential Numbers in WPS Spreadsheet

WPS Spreadsheet fully supports Excel formulas like IF and COUNTIF, allowing you to easily generate sequential numbers and process massive datasets with thousands of rows without lagging.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx or .csv file containing the large dataset.
2. Apply the sequential formula: Click the adjacent empty cell (e.g., B2) and enter the =IF(A2=A1,B1+1,1) formula.
3. Auto-fill the sequence: Double-click the fill handle on the cell's bottom-right corner to instantly populate the numbering down the entire column.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx).Optimized performance engine for processing large worksheets with tens of thousands of rows smoothly.Free to use with a lightweight installation and highly familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my IF formula return an error instead of restarting at 1?

Ensure that your cell references are correctly aligned. If your data starts in row 2, the formula must reference the cells exactly one row above (e.g., A1 and B1). Also, check that your reference column doesn't contain hidden trailing spaces, which makes identical names appear different to the formula.

Will the COUNTIF method work correctly if my data is filtered?

The standard COUNTIF formula counts all rows in the specified range, including hidden or filtered rows. If you need to count only visible rows sequentially, you would need to use a more complex combination of the SUBTOTAL and OFFSET functions.

How do I convert these formula results into static numbers?

Select the entire column containing your generated numbers, press Ctrl+C to copy them, then right-click the same selection and choose 'Paste as Values' (usually represented by an icon with '123'). This removes the underlying formulas and keeps only the static sequential numbers.

Can I add a text prefix to the sequential numbers, like 'ID-1'?

Yes, but you shouldn't add it directly inside the calculation column, as 'B1+1' will result in an error if B1 contains text. The best approach is to keep column B for the pure number calculation, and create a new column C with the formula ="ID-" & B2 to display the prefixed values.

Excel & Data Problems