Function Problems

How to Generate Sequential Customer IDs from Invoice Numbers in Excel

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to generate sequential customer IDs based on a list of invoice numbers, where duplicate invoice numbers retain the same customer ID and new invoice numbers increment the ID by one.

Product: Excel
Device & OS: not provided
Scenario: Assigning unique and sequential customer IDs to a sorted list of invoice entries without manual typing.
Observed behavior: The user requires an automated formula to replace manual data entry for assigning and incrementing customer IDs correctly based on invoice number changes.

Before you start

Before applying the formula, ensure your invoice data is sorted sequentially by the invoice number column. This guarantees the formula correctly identifies duplicate entries that belong to the same customer.

Solution 1Recommended

Use an IF Formula for Consecutive Invoice Numbers

This is the most efficient method when your invoice numbers are grouped or sorted consecutively, allowing Excel to compare each row directly with the one above it.

The IF function can logically check if the current row's invoice number matches the previous row. If it matches, it duplicates the previous Customer ID; if it does not match, it increments the previous Customer ID by one.

Enter the starting Customer ID

Click on the first data cell of your Customer ID column (for example, A2) and manually type your starting number, such as 20413. Press Enter to confirm.

Input the IF formula

Select the cell directly below your starting ID (e.g., A3). Assuming your invoice numbers are in column B, click the formula bar and type `=IF(B3=B2,A2,A2+1)`.

Apply the formula to the column

Press Enter to apply the formula. Then, click the small square (fill handle) in the bottom-right corner of cell A3 and drag it down the column to automatically generate IDs for all remaining invoice numbers.

Requirement for Scattered Data: This formula requires identical invoice numbers to be in adjacent rows. If your duplicate invoices can appear in different locations across the sheet, you must either sort the column first or use a unique-invoice mapping table with a lookup formula.

Work Smarter with WPS

Automate Sequential IDs Instantly in WPS Spreadsheet

WPS Spreadsheet provides a highly compatible and intuitive environment for using logical formulas like IF, helping you automate sequential data entries instantly without manual errors.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your invoice numbers.
2. Sort the data: Highlight your data range, navigate to the Data tab, and click Sort to organize the invoice numbers consecutively.
3. Apply the formula: Enter your starting ID in the first row, then use the IF formula `=IF(B3=B2,A2,A2+1)` in the subsequent row and drag the fill handle down.

Fully compatible with Microsoft Excel (.xlsx) formulas and formattingLightweight software with fast processing for massive datasetsBuilt-in intelligent data sorting and duplicate removal toolsFree to download and highly user-friendly interface

Frequently Asked Questions

Why does my customer ID increase for duplicate invoices?

This usually happens if your data is not sorted properly. The simple IF formula relies on adjacent cells having identical values to keep the ID the same. Select your data and use the Sort tool on the invoice column first.

Can I start my sequential ID at a specific custom number?

Yes, you dictate the starting point. Simply click the first cell of your Customer ID column, manually type your desired starting number (e.g., 1000 or 50000), and then apply the IF formula to the cells below it.

What should I do if duplicate invoices are scattered and cannot be sorted?

If you cannot sort your dataset, you must create a separate unique-invoice mapping table. You can then use lookup functions like VLOOKUP or XLOOKUP to match the scattered invoice numbers back to their assigned unique Customer IDs.

Excel & Data Problems