Create a custom auto increment based on a condition
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?
Answers
-
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)
-
Thank you so much, Paul! This was very helpful :)
-
Happy to help. 👍️
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives