Two sets of generated task numbers based on Prefix

Ashley McAdoo

Hello,

I have a sheet that is tracking submitted occurrence reports. I need the numbers to be sequential for two different prefixes OR- and SAFOR-. Example OR-1000, OR-1001, OR-1002 AND SAF-1000, SAF-1001, SAF-1002. I don't think I can do this with an auto-number column, but any suggestions on how to do this?

Thanks,

Ashley

Paul Newcome

You would use an auto-number column, but you would have it set up with no modifications. You want it to just output numbers.

From there you would use a text/number column with a formula to generate your IDs. It would start with an IF statement to determine which prefix

=IF(this = something, "OR-", "SAFOR-") +

Then a COUNTIFS to get your incrementation.

=IF([Column Name]@row = "something", "OR-", "SAFOR-") + (1000 + COUNTIFS([Column Name]:[Column Name], @cell = [Column name]@row, [Auto-Number]:[Auto-Number, @cell <= [Auto-Number]@row))

Ashley McAdoo

Thank you, I have it working now. I appreciate the help!

Paul Newcome

You would use an auto-number column, but you would have it set up with no modifications. You want it to just output numbers.

From there you would use a text/number column with a formula to generate your IDs. It would start with an IF statement to determine which prefix

=IF(this = something, "OR-", "SAFOR-") +

Then a COUNTIFS to get your incrementation.

=IF([Column Name]@row = "something", "OR-", "SAFOR-") + (1000 + COUNTIFS([Column Name]:[Column Name], @cell = [Column name]@row, [Auto-Number]:[Auto-Number, @cell <= [Auto-Number]@row))

Ashley McAdoo

@Paul Newcome,

Thanks, and I almost have it working. Here's a screen shot of my columns with the formulas - the "=" so you can see the whole formula. I'm getting unparsable in the Countif column, so I guess something needs tweaking?

Auto Number.pdf

Uploaded Jul 8, 202428 KB

Thanks,

Ashley

Paul Newcome

You missed the opening parenthesis immediately after the COUTNIFS function. Also looks like you have the reference to the auto-number column spelled wrong in the formula.

You have

[Auto Number]@row

But the column name in the sheet is Auto-Number (with a hyphen instead of a space.

Ashley McAdoo

Thank you, I have it working now. I appreciate the help!

Paul Newcome

Happy to help. 👍️