Auto number column giving duplicated numbers

Hi All,

I have an auto number column that I use in a formula to get row# but that auto number column is producing duplicate numbers and therefore messing up the formula.

Does anyone know why it is giving duplicate numbers?

Thanks.

Best Answer

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    I got it. Change the Starting number to the number you want for a new row. Hope this works.

    Screenshot_20230803-201230.png



    I love processes, worflows, automation, co-operation and technology. That's why I built a mini ERP solution for businesses.

Answers

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    Hi Carmen,

    It's strange to hear that. Can you show a screenshot to demostrate your situation?


    I love processes, worflows, automation, co-operation and technology. That's why I built a mini ERP solution for businesses.

  • Hi Gia,

    谢谢你让英航ck.

    Sure here are some screenshots:

    The auto number row is called "RowID"

    image.png

    And here are some duplicated numbers:

    image.png


  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

    The numbers in RowID are not in the order of increasing. It seems that the duplicated rows (ex 3624, 3625 at the bottom) are copied from another sheet (B) to your sheet (A). You may want to check this in activity log as below screenshot. If it is the case, you may ask for help from your internal IT.


    image.png



    I love processes, worflows, automation, co-operation and technology. That's why I built a mini ERP solution for businesses.

  • Hi Gia,

    The sheet was created by saving another sheet as a new sheet and that other sheet doesn't have this duplicate number issues.

    What's more weird is when I deleted the rows with duplicated numbers and recreated those rows by copy/paste rows within the same sheet, RowID row again produce duplicated numbers, just different ones.

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭
    Answer ✓

    I got it. Change the Starting number to the number you want for a new row. Hope this works.

    Screenshot_20230803-201230.png



    I love processes, worflows, automation, co-operation and technology. That's why I built a mini ERP solution for businesses.

  • Thank you Gia,

    I will do this and hopefully won't see this error again.

\"Printing\" the dashboard will create a pdf.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108540,"type":"question","name":"How can I update a checkbox on a sheet based off of criteria from another sheet?","excerpt":"I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX \/ MATCH function but haven't had any luck because the situations seem to be a little different than mine. Here is my situation: Sheet 1: Time Tracking - updated by each employee each week via a…","snippet":"I'm trying to update a checkbox on a sheet based off of criteria from another sheet. I've seen several examples using the INDEX \/ MATCH function but haven't had any luck because…","categoryID":321,"dateInserted":"2023-08-04T16:05:43+00:00","dateUpdated":"2023-08-04T17:19:39+00:00","dateLastComment":"2023-08-04T22:00:55+00:00","insertUserID":144833,"insertUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":144833,"lastUserID":144833,"lastUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":34,"score":null,"hot":3382352798,"url":"https:\/\/community.smartsheet.com\/discussion\/108540\/how-can-i-update-a-checkbox-on-a-sheet-based-off-of-criteria-from-another-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108540\/how-can-i-update-a-checkbox-on-a-sheet-based-off-of-criteria-from-another-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108540,"commentID":389058,"name":"Re: How can I update a checkbox on a sheet based off of criteria from another sheet?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389058#Comment_389058","dateInserted":"2023-08-04T22:00:55+00:00","insertUserID":144833,"insertUser":{"userID":144833,"name":"Tana V","url":"https:\/\/community.smartsheet.com\/profile\/Tana%20V","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xIfp8WvfKzM!BqnoQUvuP7M!zhz8Yul5dPv","dateLastActive":"2023-08-04T22:00:27+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/FX55FDBTGBGR\/snag-130d01cb.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Snag_130d01cb.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T22:00:25+00:00","dateAnswered":"2023-08-04T18:46:52+00:00","acceptedAnswers":[{"commentID":389011,"body":"

You would use an IF\/COUNTIFS combo to count how many rows have the employee ID and the Week Starting. If that count is greater than zero (meaning there was at least one entry), check the box.<\/p>


<\/p>

=IF(COUNTIFS({Source Sheet EMP ID}, @cell = [Employee ID]@row, {Source Sheet Week Starting}, @cell = \"7\/10\/2023\")> 0, 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":108515,"type":"question","name":"What is the best way to make a report that pulls from a very large sheet (14K+ lines)?","excerpt":"My source sheet is 14K+ lines and counting. Reports are limited to 2500 lines. Even when I have broken this data down by the quarter, this report is too large to even display all the data. What is the best way to support this?","snippet":"My source sheet is 14K+ lines and counting. Reports are limited to 2500 lines. Even when I have broken this data down by the quarter, this report is too large to even display all…","categoryID":321,"dateInserted":"2023-08-03T22:34:48+00:00","dateUpdated":null,"dateLastComment":"2023-08-04T18:39:33+00:00","insertUserID":163583,"insertUser":{"userID":163583,"name":"Rachel Apostol","title":"Facilities Manager","url":"https:\/\/community.smartsheet.com\/profile\/Rachel%20Apostol","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!G5DD3ykb4-Y!peFCqaEjsfo!_LY7eglrUWX","dateLastActive":"2023-08-04T20:29:21+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-04T19:26:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":6,"countViews":46,"score":null,"hot":3382280061,"url":"https:\/\/community.smartsheet.com\/discussion\/108515\/what-is-the-best-way-to-make-a-report-that-pulls-from-a-very-large-sheet-14k-lines","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108515\/what-is-the-best-way-to-make-a-report-that-pulls-from-a-very-large-sheet-14k-lines","format":"Rich","tagIDs":[265],"lastPost":{"discussionID":108515,"commentID":389007,"name":"Re: What is the best way to make a report that pulls from a very large sheet (14K+ lines)?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/389007#Comment_389007","dateInserted":"2023-08-04T18:39:33+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-04T19:26:39+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-04T16:24:31+00:00","dateAnswered":"2023-08-04T16:20:05+00:00","acceptedAnswers":[{"commentID":388979,"body":"

Hi @Rachel Apostol<\/a> <\/p>

Do you have Grouping applied to your Report<\/a>? Grouping will limit what the report can show:<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

However if you aren't<\/em> using Grouping, the Report will allow you to page through all the data, as Paul mentioned:<\/p>

\n
\n \n \"Screenshot<\/img><\/a>\n <\/div>\n<\/div>\n

Cheers,<\/p>

Genevieve<\/p>"},{"commentID":388984,"body":"

Hi @Rachel Apostol<\/a> <\/p>

I would either remove the Grouping and simply Sort by the values you're grouping by so you can manually page through the data, or set up multiple, grouped Reports, splitting up your information into smaller chunks (such as date ranges or specific grouped values).<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":265,"urlcode":"Reports","name":"Reports"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4915,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics