Generating a Distinct Identifier in Excel: Tips and Techniques

There are three options available to you:
1. Remove sheet protection.
2. Use a Worksheet_Change event to capture cell changes and write the ID there instead of using the UDF.
3. Use a UDF that writes the ID into the cell value instead of saving it to the ID.
However, it’s important to note that using a UDF to make a permanent mark on the sheet can be problematic as it’s designed for calculating cells. It will only work if you explicitly set the cells to be a new formula.


Solution:

To obtain incremental values in D for each group, simply copy and paste this formula into cell D2 and drag it down until the end.

=IF(A1 <> A2, 1, D1 + 1)

Frequently Asked Questions