Excel: Add a Group Number to Each Set of Records That Has a Unique Customer Number

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I have a list of invoice data. I want to number the records in such a way that the invoices for the first customer all have a group number 1 and the invoices for the next customer all have a group number 2.

Strategy: You can do this by sorting the data by customer. You need to add a new column A, with the heading Group. In cell A2, you enter the number 1 for Group 1. In cell A3, you enter the following formula, which will be used for the rest of the records:

=IF(C3=C2,A2,1+A2)

In plain language, this formula says, "œIf the customer on this row equals the row above, then use the group number on the row above. Otherwise, add 1 to the group number above." You need to copy this formula down to all the other rows.

  1. Assign each customer a group number.

    Results: Each record will be assigned a group number. Each customer will have a unique group number.

    In order to allow future sorting, you copy the formulas in column A and use Home, Paste dropdown, Paste Values to convert the formulas to numbers.