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

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. 👍️