Formula for Unique ID's following a predefined rule

Closed
Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month