Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

Unique ID Formula

Hi everyone,

I am creating a sheet to gather project submissions. The unique ID feature doesn't work in my case. I have 13 departments, lets say Finance, Health, and Education as an example. Ideally, I would like to generate an ID such as: FIN-001, and if a second finance project were to happen it would appear as FIN-002. If it were education and EDU-001, etc.

I have tried to use JOIN(LEFT(DEPT@ROW,3),"-", COUNTIF(and now I'm stuck...

Thanks

Best Answers

Answers

  • Overachievers Alumni

    I think this will do it. I didn't bother with leading 0s, but you can add an IF to the count to add those 0s.

    =LEFT(DEPT@row, 3) + "-" + COUNTIF(DEPT1:DEPT@row, DEPT@row)

    The key to this is that you're doing the Count from the first DEPT row to the current row, so you count the number of instances that department has been listed.

  • ✭✭✭✭✭
    edited 10/15/20

    Thanks @David Tutwiler , I think this just needs some fine tuning. These types of formulas are way above my head, this is what I'm getting:


    I will add that these rows are being submitted by an online form (Project ID is not on the form of course) and the form adds rows to the top of the sheet.

  • Overachievers Alumni

    @Lila De Vera Looks like the one on the row you put the formula in is working correctly. Did you copy the formula all the way up? Or better yet, make it a column formula?

    Or is it not returning as you expected it to?

  • ✭✭✭✭✭

    @David Tutwiler, yes I copied the formula all the way up.

    for some reason the range portion of the COUNTIF isn't adjusting by row. This is what it's looking like. If I manually adjust the range it does seem to work, but not automatically.


  • ✭✭✭✭✭
    Answer ✓

    Amazing, works like a charm. Thank you @David Tutwiler

  • Overachievers Alumni

    Awesome! Glad to hear it.

  • ✭✭

    This works like a charm - thank you @David Tutwiler

    How do you add digits to the outcome, though?

    Using the example above, to have the output look like "Fin-002"

  • Overachievers Alumni

    @SPBops You would have to evaluate the number and then add the leading zeros with a formula. In this scenario, the cleanest way to do it would probably be to evaluate the number while you are doing the COUNT statement. If the count is less than 10, then you would need to add 2 leading zeros (for 100 based systems. Modify the number of zeroes needed based on how many digits you plan on needing). If it is less than 100, then it needs 1 0. Here is a modification of the formula that should do that:

    =LEFT(Department@row, 3) + "-" + IF(COUNTIF(Department$1:Department@row, Department@row) < 10, "00" + COUNTIF(Department$1:Department@row, Department@row), IF(COUNTIF(Department$1:Department@row, Department@row) < 100, "0" + COUNTIF(Department$1:Department@row, Department@row), COUNTIF(Department$1:Department@row, Department@row)))

  • ✭✭

    Ah yes - was hoping it would be a little simpler, but I see that's not likely. Very good. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions