Formula for Unique ID's following a predefined rule

Zolliez - Oct 12, 2017 at 03:36 PM - Latest reply: TrowaD 2367 Posts Sunday September 12, 2010Registration dateModeratorStatus June 19, 2018 Last seen
- 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.

See more 

Your reply

1 reply

TrowaD 2367 Posts Sunday September 12, 2010Registration dateModeratorStatus June 19, 2018 Last seen - Oct 16, 2017 at 12:16 PM
0
Thank you
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
Respond to TrowaD