Auto Number Column Duplicating

CaraBart28

Good Morning,

I have an automation setup for when an individual orders an XL the row duplicates for the Standard size along with the XL.

What I cant figure out is why my auto number duplicates instead of applying the next auto number.

In the screen shot below you will see that the third row is a duplication of the second row however one row is for an XL blood pressure cuff and one is for a standard cuff. I am not able to figure out why the auto-number is duplicating for these instances.

SteyJ

https://community.smartsheet.com/discussion/114513/auto-number-column-duplicating

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)

CaraBart28

@SteyJ That is actually how it is setup, the "Order Number" is a system auto-number column and the "Master Order Number" column is a formula based column for sequential ordering. I am not able to figure out why the system auto-number column is duplicating the "Order Number"

SteyJ

https://community.smartsheet.com/discussion/comment/410083#Comment_410083

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

CaraBart28

@SteyJ It is still set as auto-number, the auto-number transfers in from other sheets. There are 9 sheets which transfer into the master sheet, each of them have the auto-number setup and it transfers into the master sheet.

Toufong Vang

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.

Toufong Vang

=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.

CaraBart28

@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.

Toufong Vang

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

CaraBart28

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

Toufong Vang

@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.

CaraBart28

@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.

Toufong Vang

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

CaraBart28

@Toufong Vang I did try the second route, however, I used data mesh as an automation to load data back into the master sheet and when that happens the modified date for the original and duplicate row are the same so therefor it will not provide me with a unique identifier.

Toufong Vang

@CaraBart28 I haven't used DataMesh. Instead of copying/duplicating the entire row, will DataMesh allow you to leave a few columns empty (unmapped)? If so, then leave the Created Date and Modified Date timestamps blank; and leave the auto-number column blank, too. When the sheet is refreshed, these fields will be filled-in by Smartsheet, thereby making them different from that of the original row.