Number Each Record, Starting at 1 for a New Customer

November 01, 2022 - by Bill Jelen

Problem: I have a list of invoice data. I want to number the records in such a way that the first invoice number for Ford is 1. The next Ford invoice is 2, and so on. When I get to a new customer, I want to start over at 1.

Data is sorted by Customer. You want to add a record number column. For example, when the customer changes from Ford to Verizon, the record number will start over at 1 and the other Verizon records would be numbered 2, 3, and so on. — Figure 790. You want to add sequence numbers within each customer.

Strategy: Use a formula in a new column A to add the record number. Follow these steps.

1. Select one cell in the customer column and select Data, AZ to sort the data by customer.
2. Insert a new temporary column A and add the heading Rec # to A1.

In A2, enter the formula =IF(C2=C1,1+A1,1). In plain language, this formula says, “If the customer in C is equal to the customer above me, then add 1 to the cell above me. Otherwise, start at 1.” Copy the formula down to all rows. Excel will number each group of customer invoices from 1 to N. When a new customer starts, the numbers will restart.

The results of the Record Number column. There must be 52 rows for the customer Ainsworth. The last Ainsworth row in A53 has a record number of 52. When Air Canada occurs in C54, the record number resets to 1, followed by 2, and so on. — Figure 791. The live formulas work while the data is sorted.

Change the formulas in A using Ctrl+C, Home, Paste, Paste Values before sorting by invoice number.

Alternate Strategy: You can use the formula =COUNTIF(C$2:C2,C2) without sorting.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jon Tyson on Unsplash