Create a custom auto increment based on a condition

BobWhite

I am trying to generate unique ID codes for each line item. The ID codes are: TYPE + "-" + YEAR + "_"+ #ofTypeinYear + "_" + PLACE.

I am struggling to create the "#ofTypeinYear" column. This column should be able to scan the ID CODE column, and find the max "#ofTypeinYear" in the code for the items that have the same type and same year, and increment by one.

The sheet has an associated form, and every time there is a new submission row, I would like to autogenerate a unique ID Code. Do I need to create a series of linked sheets to do this?

Paul Newcome

You would need to insert an auto-number column (called "Auto" in this example) with no special formatting. Then you would use a COUNTIFS along the lines of

=COUNTIFS(Type:Type, @cell = Type@row, Date:Date, IFERROR(YEAR(@cell), 0) = YEAR(Date@row), Auto:Auto, @cell <= Auto@row)

BobWhite

Thank you so much, Paul! This was very helpful :)

Paul Newcome

Happy to help. 👍️