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
-
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 "Fin-002"
-
@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
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!