Auto Number Duplicates

dustinp

I have a sheet with a column called "Tape Numbers" and want it to auto assign first available number. I started using Auto Assign but realized it was duplicating numbers that already existed. How do I get sheet to atuo assign a unique number without duplication?

Hollie Green

You will have to go back and correct the ones already duplicated but to prevent future duplication once you have them all corrected right click on the column header and click on edit column properties and put in a starting number. The next entry will be whatever you put as your starting number and will continue consecutively.

dustinp

Issue I have is I don't have a good place for starting number. For example, looking for a way to auto number and select first free number starting with 10000 and 10002 already exists so I don't want it to get duplicated.

Georgie

Hi @dustinp,

Would resetting the auto-number column be an option for you? If so, the steps to do so can be found here:

• Reset the auto-number column

Hope that helps!

Georgie

dustinp

No, I can't delete the auto number column and need to retain current values.

Michelle Choate 2

@dustinp There is not a way to auto assign the first free number from a set. You can only set a starting place, and it will number up consecutively from there for an auto-number column. Alternatively, as @Hollie Green said, you can change the column to text/number, alter the incorrect ones that currently exist, then select a good starting point for auto numbering when you change the column to an auto-number column.

Toufong Vang

Hi, @dustinp , another option is to leverage the timestamp, "Created Date". Like the auto number, the created date stamp is persistent for the row. And, in our experience, it's unlikely that two rows with the same auto number will be created at the same time.

The expression below will return the timestamp.

SUBSTITUTE(""+YEAR(CreatedDate@row) + RIGHT("0" + MONTH(CreatedDate@row),2) + RIGHT( "0" + DAY(CreatedDate@row),2) + RIGHT( "0" + TIME(MID(CreatedDate@row, FIND(" ", CreatedDate@row), 10), 1),5), ":", "")

Then append your auto number…

= time_stamp_expression + RIGHT("00000" + AutoNumber@row, 5)

This won't work if your processes make copies of rows. The duplicate of a row will always include its created date and auto number.