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
Check out the Formula Handbook template!