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: FIN001, and if a second finance project were to happen it would appear as FIN002. If it were education and EDU001, etc.
I have tried to use JOIN(LEFT(DEPT@ROW,3),"", COUNTIF(and now I'm stuck...
Thanks
Best Answers

Ok, so the problem (I think) is that there isn't an absolute reference on the first Department. Let's try this:
=LEFT(Department@row, 3) + "" + COUNTIF(Department$1:Department@row, Department@row)

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

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.

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.

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

Ok, so the problem (I think) is that there isn't an absolute reference on the first Department. Let's try this:
=LEFT(Department@row, 3) + "" + COUNTIF(Department$1:Department@row, Department@row)

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

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 "Fin002"

@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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.2K Get Help
 320 Global Discussions
 197 Industry Talk
 415 Announcements
 4.2K Ideas & Feature Requests
 126 Brandfolder
 153 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 276 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!