Assign unique increment ID based on two conditions

[troyski]

Hi everyone,

I've always been a big fan of Mr. Excel's YouTube videos and thought I'd participate in the forum as well. My first post is in relation to the project I am working on. I am hoping somebody could help me out. Been searching for hours on the web and stumbled upon some possible solutions but only to open more questions and challenges. Now I'm stuck.

Project Objective: To create a document control database.

Brief background: My manager wants me to create a database that will stricly control the issuance/assignment of unique document control ID. This ID will have the site and department prefixes followed by the unique incremental ID, e.g. AKL-HR-001 for Auckland - Human Resouces - record #1, HAM-SS-001 for Hamilton - Sales - record #1, AKL-HR-002 for Auckland - Human Resouces - record #2, HAM-HR-001 for Hamilton - Human Reources - record #1, etc...

Actions done:

• Created database using Format as Table
  
• Set up two tables for List data validation source and VLOOKUP.
  
  Table 1 Site Table (e.g. Site Name is Auckland and the Site Code is AKL)
  Table 2 Department Table (e.g.Human Resources is HR)
• Setup List data validation under columns A (Site) and B (Department) in database
• Created a formula that will lookup the two tables and concatenate the prefixes, and then assign a unique ID in Column C (Assigned Code). Formula I used is:
  
  =VLOOKUP(A2,$F$2:$G$5,2,FALSE)&"-"&VLOOKUP(B2,$F$8:$G$13,2,FALSE)&"-"&COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2)
  
  The main idea is, when a user selects a site (column A) and a department (column B) from a drop down list, it automatically assigns an incremental number using the COUNTIFS function. The end result is the Assigned Code, e.g. AKL-HR-001. The formula was successful in assigning unique incremental ID based on site and department selections, however, as I continue to add new records, I see double up IDs.
  
  I checked the formula of the last entry and couldn't figure out what's cousing the double ups. Below is the formula of the last record:
  
  =VLOOKUP(A12,$F$2:$G$5,2,FALSE)&"-"&VLOOKUP(B12,$F$8:$G$13,2,FALSE)&"-"&COUNTIFS($A$2:$A12,A12,$B$2:$B12,B12)
• Also, I've noticed that when I insert a new record in between two existing records with ID assignments, it resets the assignments.
  
  For example, I inserted a row in between rows 1 and 2 and it reassigned what was previously there to ID #3 (instead of keeping ID #2). What I was hoping to get as a result was, once a record has been assigned with the ID, it keeps it even if new records are inserted in between.

Forgive me for the long explanation. I couldn't think of a shorter way to explain what I was wanting to achieve. Is there a much better and smarter way of doing it? Would appreciate any help from the community as I've reached my dead end

 

[1hsan]

Hi,

Here are the formulas I used.

if you insert rows, the formulas will calculate and the ids will change. so always add new items at the bottom of the list.

 

[troyski]

Hi 1hsan

Thank you very much for replying to my post. What I was wanting to know is a way to ensure that the assigned codes is fixed to the record with no dependency on formulas being recalculated each time a new record is inserted in between current records. I am wanting to make the database 'idiot-proof' as there will be a group of users with varied Excel skills. I can leave a 'warning', yes, stating that they should only add new items at the bottom of the list but I am wondering if there is a way to keep the code to that specific record? VBA maybe?

Thanks again,
Troyski