Excel Generate a Unique ID for Each Category

Problem description

Excel data as shown:


We are trying to recode UNIQUE ID field. The rule is like this. Same IDs have same UNIQUE IDs. The expected result is the right part in the following table:



Use the SPL XLL plug-in of Excel

Write the formula in a blank cell:

As Shown:


Group A1’s data by ID, loop through each group to assign the current group number to variable a, and then loop through each record of the current group to assign a to UNIQUER ID.

Q & A Collection