Formula for Unique ID's following a predefined rule

Ask a question Zolliez - Last answered on Oct 16, 2017 at 12:16 PM by TrowaD

I am in need of a formula that can deal with the following situation:

We have many projects each year. Each new project gets assigned a 4 digit ID (i.e. 2890). The next project will get the next number up (i.e. 2891) - This is easy as it is a +1 on the last value.

The next step is that each project gets assigned a production number, which is composed of the 4 digits in the project number, followed by 1.1 (for the first one - i.e. 2890.1.1)

However, our complication arises when there is a second production number (a new order on the same project, an add on etc.), where we want it to assign the next production number (i.e. 2890.1.2). Sometimes our projects have phases, and a phase would invoke a change to the second last number (i.e. 2890.2.1).

An example below:

Date: 10/10/17
Customer: Joe
Project name: Doors
Project ID 2890
Production #:2890.1.1

Date: 11/10/17
Customer: Joe
Project name: Doors (Add on #1)
Project ID 2890
Production #:2890.1.2

Date: 12/10/17
Customer: Joe
Project name: Doors Phase 2
Project ID 2890
Production #:2890.2.1

How can the production number be automated via an excel formula?

Thanks in advance.

plus moins
Hi Zolliez,

Give the following formula a try:
=IF(ISERROR(SEARCH("Add on",B9)),IF(SEARCH("Phase",B9)>0,REPLACE(B5,6,1,MID(B5,6,1)+1)),REPLACE(B5,8,1,RIGHT(B5,1)+1))

Cell B9 refers to the cell with the project name of the 2nd project (in this case Doors (Add on #1) ).
Cell B5 refers to the cell with the production # of the original project.

Best regards,
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!