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

November 02, 2022 - by Bill Jelen

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.

Data is sorted by customer. You would like to add a new Group column with a new number for each group of customers. In this example, Ainsworth would be assigned to Group 1. The next customer, Air Canada would be assigned to Group 2. Bell Canada would be assigned to Group 3. The formula in A54 is =IF(C54=C53,A53,1+A53). Customers are in C. — Figure 792. 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.

This article is an excerpt from Power Excel With MrExcel

Title photo by Sophie Elvis on Unsplash