Formula for Unique ID's following a predefined rule

Closed
Zolliez - Oct 12, 2017 at 03:36 PM
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 - Oct 16, 2017 at 12:16 PM
Hi,

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.

1 reply

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 16, 2017 at 12:16 PM
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,
Trowa
1