Pin Me

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

    Images

    Fig. 810Fig. 811Fig. 812Fig. 813