Custom Auto-Number?

Elyssa Guthoff

Question on Auto-Number column capabilities. I want to add a column to my portfolio sheet that will assign a unique project ID, like the below example, to each project row. I have columns on the same sheet that designate project class & start date. I'd like to use the auto-number feature to look at the start date and project class of a project, and assign a unique id to it. Is getting to the below example, or something close to it possible? Anyone know how to do this?

Example of Unique Project ID: R-12-20

 R = Renovation (Project Class)

12 = Project No. 12

 20 = Year when project was approved (so that each year we could restart project numbers)

Paul Newcome

It is possible. Are you able to provide a screenshot with sensitive/confidential data removed, blocked, and/or replaced with "dummy data" as needed?

Elyssa Guthoff

I created a quick grid with dummy data below. Thank you for your help!

Paul Newcome

Ok. So I can tell that the leading letter is the first letter from the [Project Class] column.

I am assuming that the last portion of the ID is the year, but for R and I you have 2 digits and for C you have all 4. I want to confirm that this is correct?

Finally... How exactly are you determining the number in the middle section? I can't find a pattern/reference.

Elyssa Guthoff

Sorry I had a typo!

Yes: the leading letter is the first letter from the [Project Class] column.

Yes: the last portion of the ID is the year, only need two digits

Middle section is where i want the auto number to come in, assigning each project its own number. Does that need to be a separate, hidden column, then combine all fields with a formula? or is there another way? Want the final [Project ID] result a unique ID for the project field, or row, on my portfolio sheet.

Thank you!

Paul Newcome

It doesn't need to be a separate column. I just want to make sure I understand the logic. I don't notice a pattern for it though.

You have

1

2

3

1

How exactly did you determine those numbers?

Elyssa Guthoff

No pattern or logic. I was just typing in random dummy numbers. Was assuming the same number could be used more than once if its associated to a different Project Class. The middle number can be any number, as long as the end result is a Unique Project ID.

Paul Newcome

Ok. So how about this...

=LEFT([Project Class]@row + "-" + COUNTIFS([Project Class]$1:[Project Class]@row, [Project Class]@row, [Start Date]$1:[Start Date]@row, YEAR(@cell) = YEAR([Start Date]@row)) + "-" + RIGHT(YEAR([Start Date]@row), 2)

This will pull the first letter from the [Project Class]. Then it will give a sequential count of how many previous rows had that same [Project Class] in that same year. Finally it will pull the right two digits of the year.

So an example output of the above would be...

R-1-20

R-2-20

I-1-20

C-1-20

R-1-19

R-3-20

I-2-20

C-1-19

Does that work, or would you like something different for the middle portion?

Elyssa Guthoff

That is perfect. Thank you so much for your help!

Paul Newcome

Happy to help! 👍️

Akellu

Hello, I would like to add an auto custom number column to a sheet. The format is YY-MM-sequence in 3 numerical digits. We start the sequence over at 001 at the beginning of each month which is a piece to this function that I can't seem to get. Here's how far I've gotten:

=LEFT(YEAR(Created@row))+"-"+(MONTH(Created@row))+"-"+(COUNTIF(Inquiry@row)created)

Akellu

@Paul Newcome you're such a formula/function guru, are you able to help with the above?

Paul Newcome

@Akellu Try something like this...

RIGTH(YEAR(Created@row), 2), + "-" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 10, "00", IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 100, "0")) + COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row))

Akellu

Hi @Paul Newcome, thanks for your help. I copied your formula and still got Unparsable. I updated the column references, indicated by yellow dot so you can easily see. It's still not working. Any additional help or insight is greatly appreciated.

Paul Newcome

My apologies. I had a comma that snuck in where it didn't belong. Try this...

=RIGHT(YEAR(Created@row), 2) + "-" + IF(MONTH(Created@row) < 10, "0") + MONTH(Created@row) + "-" + IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 10, "00", IF(COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row)) < 100, "0")) + COUNTIFS(Created:Created, AND(IFERROR(MONTH(@cell), 0) = MONTH(Created@row), IFERROR(YEAR(@cell), 0) = YEAR(Created@row), @cell <= Created@row))

Raul Cabrera

Paul maybe you can help me. I am trying to do the same autogenerating reference numbers using a column that it is like the project class (What is it?) and the reference date will be the created column date that automatically assigned by system when the item is added.

I followed the formula that you put it upper but it is giving #unparseable error. I appreciate as usual your help

=LEFT([What is it?]@row + "-" + COUNTIFS([What is it?]$1:[ What is it?]@row, [What is it?]@row, [Created]$1:[ Created]@row, YEAR(@cell) = YEAR([Created]@row)) + "-" + RIGHT(YEAR([Created]@row), 2)

Then what i am trying to get in that Auto Ref Test is R-XXX-21, if the What is it? is Issue the initial letter should be I-XXX-21 and if it is Audit should be A-XXX-21

Thanks in advance