## Microsoft Excel: Number Each Record For a Customer, Starting at One For a New Customer

written by: Mr Excel•edited by: Tricia Goss•updated: 11/18/2011

Problem: You have a list of invoice data, as shown in Fig. 810. You wish to number the records in such a way that the first invoice number for Ford is one. The next Ford invoice is two, and so on. When you get to a new customer, you want to start over at one.

• slide 1 of 2

Strategy: Sort the data by Customer. Insert a new temporary column A and add a heading called "Rec #", as shown in Fig. 811.

The formula in A2 is =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."

Result: Each group of customer invoices is numbered from 1 to N, as shown in Fig. 812.

As shown in Fig. 813, copy the formulas in column A and Paste Special – Values to change to numbers so that you can re-sort the data by Invoice Number.

Alternate Strategy: A formula of =COUNTIF(\$C\$2:C2,C2) will work without sorting.

Summary: The IF function is perfect for this task of comparing the current record to the record above.

Functions Discussed:

=IF()

See all Microsoft Excel tips

• slide 2 of 2