Unique IDs for Unique Values
I'm trying to assign a unique id (sequential number) for unique values within a sheet. I've been playing around with INDEX and MATCH, but am having a hard time getting something to work. Any ideas on how to get a formula to have row "ID" list the way I need it to? It needs to assign the ID value if Tract No. already exists. If the Tract No. does not exist, it assigns it the next sequential number. I need the IDs to go in order so that the MAX number would indicate how many unique values are in Tract No.
Example:
Apple 1
Apple 1
Orange 2
Banana 3
Apple 1
Apple 1
Orange 2
Answers
-
Insert an Auto-Number column with no special formatting (called "Auto" in this example).
Then in a text/number column (called "Distinct" in this example) you would use
=COUNT(DISTINCT(COLLECT([Tract No.]:[Tract No.], [Tract No.]:[Tract No.], @cell <> "", Auto:Auto, @cell<= Auto@row)))
Finally in the column you want to have the actual ID in you would use something like this:
=INDEX(Distinct:Distinct, MATCH([Tract No.]@row, [Tract No.]:[Tract No.], 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!