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