• Excel Book Excerpt

# 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, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

to Each Set of Records
That Has a Unique Customer Number

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.

Figure 734 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.

For more resources for Microsoft Excel: