Auto Number Column Duplicating (2024)

SteyJ ✭✭✭✭✭✭

12/18/23 edited 12/18/23

Smartsheet only allows for one auto number column. You can essentially create your own custom auto number column with a formula like this:

=[Order Number]@row + " " + COUNTIF([Order Number]:[Order Number], <> "")

When a new row is created, it will assign a custom sequential order number, based on the [Order Number].

The only issue with this, if you have multiple rows being created at the same time, it will calculate their order number as the same (such as automation moving rows into a sheet)

Sincerely,

Jacob Stey

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

12/18/23

0 · Share on FacebookShare on Twitter

SteyJ ✭✭✭✭✭✭

12/18/23

I see. Try verifying that the Order Number column didn't change to a text/number type, and verify there isn't another column that has changed to auto-number

Sincerely,

Jacob Stey

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

12/18/23

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

12/29/23 edited 12/29/23

Hi, @CaraBart28 , as you have discovered, an auto number does not change, and is unique only within the sheet that it is created. When setting it up, you can configure a prefix or suffix for the auto-number column. However, doing so changes the "auto-number" to a string and prevents you from using numeric functions directly on the value (e.g., MIN(), MAX(), and others).

Here's one approach if you have a small, manageable number of rows in the master sheet. It requires minimal changes to your collection of sheets.

1. Create a Text/Column named Sheet # in all of your 9 source sheets . In the first sheet, place the column formula =1; =2 in the second sheet; =3 in the third sheet; and so on.
2. Create the Sheet # column in the master sheet, too.
3. For every row in the master sheet, figure out in which sheet the row was originally created and enter the sheet number into Sheet #. If it is too time consuming, simply ensure that all of the rows with the "duplicate" auto-number have a different number in the Sheet # column.
4. Place the column formula below into the Master Order # column.
5. Done!

=IF(AutoNumber@row = MIN(COLLECT(AutoNumber:AutoNumber, AutoNumber:AutoNumber, @cell <> "")), 1, 1 + COUNTIF(AutoNumber:AutoNumber, <AutoNumber@row) + COUNTIFS([Sheet #]:[Sheet #], <[Sheet #]@row, AutoNumber:AutoNumber, =AutoNumber@row))

Here's how the formula works...

If the auto-number for the row is the MIN(), then its master order number is 1. If not, then the master order number is...

Add 1 to the count of rows with an auto-number less than the current row AND add (the count of rows that have the same auto-number as the current row and whose sheet # is less than that of the current row).

This approach leverages the fact that other sheets in the collection may create the same number, but each sheet will create that auto-number just once.

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

12/29/23 edited 12/29/23

=1 + COUNTIF([Date Request form Submitted]:[Date Request form Submitted], <[Date Request form Submitted]@row)

@CaraBart28 , as it happens, there's an even simpler approach if the column Date Request form Submitted is the Created date column. If it is, then use the formula above.

No other changes to your sheets are necessary.

The created date value is a system timestamp, and is granular to the second--maybe millisecond, too. While not displayed in the cell, the second/millisecond granularity allows us to find the order in which the rows were created. That's what the formula above does.

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

01/04/24

@Toufong Vang Thank you for your help. I added the above reference formula, however it is still giving duplicate numbers. Please see the screen shot below.

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

01/04/24

@CaraBart28 Wow...that means that those are in fact duplicates OR they happened to be created at exactly the same moment in the system OR the Smartsheet system timestamp is not as granular as is needed by the approach.

Since using the Created Date alone doesn't work, try the first method of using a helper Sheet # column along with the auto-number column. (First suggestion above--posted on 12/28.)

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

01/04/24

@Toufong Vang I am getting the unpears when entering in the formula, i might be missing something?

=IF(AutoNumber@row = MIN(COLLECT(AutoNumber:AutoNumber, AutoNumber:AutoNumber, @cell <>"")), 1, 1 + COUNTIF(AutoNumber:AutoNumber, <AutoNumber@row) + COUNTIFS([Sheet #]:[Sheet #], <[Sheet #]@row, AutoNumber:AutoNumber, =AutoNumber@row))

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

01/04/24

@CaraBart28 Hmm...ensure that you have the necessary columns for the formula to work.

If your auto-number column is not named "AutoNumber", then replace AutoNumber@row and AutoNumber:AutoNumber with the name of the column in your sheet.

Also, the formula requires a column named Sheet #, and it must have a numeric value.

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

01/05/24

@Toufong Vang I corrected the formula so that it reflects the column name "Order Number" However I am still not getting a sequential order or numbers.

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

01/05/24

@CaraBart28 The first solution requires that each sheet has the column formula ="1" for sheet #1, "=2" for sheet #2, "=3" for sheet #3, and so on. As a result, when the copied rows get transferred into the master sheet, you may have duplicate order numbers, but only one combination of sheet # and order number.

If your workflow/process permits a row to be routed/moved back to the sheet that originally created the row, then you will need to use the Modified Date column type instead of the "Date Request form Submitted" in the formula for the second solution (post from 12/29/2023).

0 · Share on FacebookShare on Twitter

CaraBart28 ✭✭✭✭

01/18/24

0 · Share on FacebookShare on Twitter

Toufong Vang ✭✭✭✭✭

01/18/24 edited 01/18/24

0 · Share on FacebookShare on Twitter