Creating a Unique ID (YY-###)

Leeweber

I'm looking to create a unique ID per entry as YY-### that will reset each year (24-001, 24-002, 24-003, 25-001, 25-002 etc)

Row ID: (auto number column)
YYYY: =YEAR([Date Submitted]@row)
YY: =RIGHT(YEAR([Date Submitted]@row), 2)
Entry ID: =YY@row + "-" + IF(COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row) < 10, "00" + COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row), IF(COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row) < 100, "0" + COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row), COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row)))

My only issue is if I delete a row, IDs for subsequent rows in the same year change.

How can I keep 24-003 from changing to 24-002 if I delete the row of 24-002?

Thanks in advance!

Paul Newcome

You would need to use a copy row automation to send the row over to a second sheet with the new unique ID being static data and then work from there.

Also… An easier way to zero-fill (also cuts out the need for both year columns)…

=RIGHT(YEAR([Date Submitted]@row), 2) + "-" + RIGHT("00" + COUNTIFS([Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = IFERROR(YEAR([Date Submitted]@row), 9999), [Row ID]:[Row ID], @cell <= [Row ID]@row), 3)

Paul Newcome

You would need to use a copy row automation to send the row over to a second sheet with the new unique ID being static data and then work from there.

Also… An easier way to zero-fill (also cuts out the need for both year columns)…

=RIGHT(YEAR([Date Submitted]@row), 2) + "-" + RIGHT("00" + COUNTIFS([Date Submitted]:[Date Submitted], IFERROR(YEAR(@cell), 0) = IFERROR(YEAR([Date Submitted]@row), 9999), [Row ID]:[Row ID], @cell <= [Row ID]@row), 3)

Leeweber

Thank you Paul!

Paul Newcome

Happy to help. 👍️