I've been searching the web for a way to generate a unique, sequential alpha-numeric value (i.e., MFTP001, MFTP002, etc.) for each new 'project' entered into a worksheet.



The generated value needs to be permanently 'assigned' to that project and not change throughout its lifespan, regardless of the addition/deletion of rows around it.

Ideally, the value will populate its cell when a value in entered into one of four other cells on the same row (e.g., different project types).

So far, everything I have found only covers 'dumb' sequential numbering or numbering of rows 1 through whatever, that auto-number when a row is added or deleted.

Is this possible?

Thanks in advance!

Using Power Query, here is the Mcode. If you add data to the source table and click on Refresh All on the Data Tab, it will automatically update the output. Try it on my sample attached.

Attached Files

PQ Sort alphanumeric.xlsx (17.5 KB, 5 views)

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505

Hello again, Alan!

First, love that Avatar!

Correct me if I'm wrong, but does this query simply sort created data, alpha-numerically?

If so, I am trying to find a way, if possible, to generate a sequential, alpha-numeric number that will automatically generate and be permanently assigned (in column A) to a project when it is entered into this worksheet (by selecting a value in one of the columns E, F, or G).

If not, then I am not exactly sure how to enter the Mcode solution you've sent...

I've included a copy of the worksheet in question...

Attached Files

EXAMPLE_Worksheet.xlsm (82.4 KB, 4 views)

You are correct. My code simply sorted the alpha numeric sequence.

__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505

On fly (I can't access excel currently):

I'm assuming it is a Projects table where you are registering new projects?

When yes, then instead having 4 columns for project types, have a single one, where you register the project type (probably values like "MFTP";
Then create a dynamic name (e.g. nProjType) which returns project type in current active row;
Then create a dynamic name, which calculates MAX value of numeric part 1*(last 3 characters) of all project numbers starting with same current project type string as in current row (e.g. nProjNo). The easiest way to get this is to have in Projects table a hidden column, where numeric part of project number for current row is calculated for all rows with same project type as in active row;
Then add an hidden sheet with formula in some cell like =IIF(ProjType="","",ProjType & RIGHT("000" & ProjNo+1, 3)), and define this cell as name also (like lProj - I use prefics l as this name is used to define a list - which will contain a single value btw.). This name returns now next project number for active row in Projects table;
In projects table, define project number column fields as Data Validation List with value = lProj. As result, the only value you can enter or select for project number column will be the new project number on hidden sheet.

NB! With setup described above, when you select new project number for row with non-empty project number, then old one will be replaced with new one. You can avoid this, when in formula on hidden sheet you check for current project number, and set the value of lProj as empty string in case the current project number <> "". I.e. you define another dynamic name e.g. nProj, and the formula on hidden sheet will be like
=IIF(OR(ProjType="",Proj <>""),"",ProjType & RIGHT("000" & ProjNo+1, 3)).
When project number in current row is not empty, the data validation list will be empty, and you can't select it.