Unique ID Formula

Lila De Vera

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

David Tutwiler

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)

Lila De Vera

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

David Tutwiler

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.

Lila De Vera

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.

David Tutwiler

@Lila De VeraLooks 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?

Lila De Vera

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

David Tutwiler

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)

Lila De Vera

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

David Tutwiler

Awesome! Glad to hear it.

SPBops

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"

David Tutwiler

@SPBopsYou 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)))

SPBops

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