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.
Add a Group Number 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:
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.