Generating Semi Unique ID's based off Multiple Criteria's

pbsmith82

New Member
Joined
May 22, 2019
Messages
8
I am trying to have the spreadsheet to Auto Generated an ID for me. I need this ID to Reference together 3 different Products into a Single Project.
At the bottom I have included a link to a Sample Workbook & Picture.


ENTERPRISE PROJECT A: (Composed of three different Products)


  • MIL Site | ID: 10071E
  • CRM | ID: 10071E
  • IH & Lite CMB | ID: 10071E


ENTERPRISE PROJECT B: (Composed of Two different Products)


  • MIL Site | ID: 10071E.1
  • IH & Lite CMB | ID: 10071E.1




The basic desire of the formula would be as follows:
If Enterprise is Yes, & Parent Company Matches, I need it to copy the first 5 digits of the Account Number, put an E on the end. I need it to date for all 3 Products. If that Enterprise ID has already been Created for that proticular PRODUCT it was add a ".1"
So it would be possible and desired that a MIL SITE, CRM, and IH & LITE CMB would all have the same ENTERPRISE ID, but that ID could NEVER appear more than 3 Times.

HERE IS A LINK TO SAMPLE WORKBOOK: http://phillipbsmith.com/wp-content/uploads/2019/05/Excel-Guru-1.xlsx

 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,095,990
Messages
5,447,772
Members
405,464
Latest member
Jayne125

This Week's Hot Topics

Top