Auto Generated Unique ID

Hello Smart Heads! Happy New Year!

I am facing an issue with big data sets, missing a single unique ID.

The only option of creating an unique ID is by joining content from 3 various cells, see:

id is created by using simple formula:

=EntryNumber@row + BLNumber@row + ContainerNo@row

In a raw file (excel of course), I have created the same column with:

=CONCATENATE(A255,G255,H255)

  • Important about raw file:
  • rows can change overt time, hence it is required to update the unique rows on the weekly basis

I have created a data shuttle set up to update the master file with weekly raw data:

The main issue (I suppose) is with id column generated via formula. Therefore I cannot link it with id column created in excel:

Desired outcome:

  • Master file (quarterly - considering size limit) with rows updated on the weekly basis (both new and updated)
  • Manual effort: 1) download file, 2) upload to smartsheet
  • Reporting (not in scope of this request, i should be able to manage, as soon as I ensure maintenance of unique rows).

Thanks in advance for your help, please let me know in case of any questions!

Romano

Answers

  • Julie Fortney
    Julie Fortney Overachievers

    @Romano el Polako

    You should still be able to use the id column as your unique ID, but you don't need to map it. Just map the columns that make up your id column formula: EntryNumber, BLNumber, and ContainerNo.

    Does that help?

  • Hey @Julie Fortney

    Sorry for delay and thank you for your answer.

    Actually it did not help, as I had to anyway create similar column manually in excel before upload, which was not a huge automation anyway.

    Nevertheless, believe it or not, requesters forgot to mention one column at the very end of the raw file (deleted in the very beginning of project scoping as non-value added) with unique id provided by broker.

    As a result I was able to feed the master file. The only problem might be the size limit of 20k rows, but hoping for the best and lack of limits in the near future.

    Thanks,

    Romano

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!