Unique IDs for Unique Values

Kelsey Ciarrocca

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

SmartsheetUniqueID.png

Uploaded Jan 10, 202323.35 KB

Paul Newcome

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))