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.


Apple 1

Apple 1

Orange 2

Banana 3

Apple 1

Apple 1

Orange 2


  • Paul Newcome
    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))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!