Formula for sequential ID with letters and numbers

[mbowick]

Formula for sequential ID with letters and numbers
Hi All

My first post here so apologies if I get something wrong! I currently have a table within Excel 365 that records projects. Each project has a unique sequential ID. To get the table to autofill each new row I need a formula in the project ID that can be carried over, however all my tries of using a various of formulas ROW, TEXT, SEQUENCE, CONCATENATE etc. has failed. I can't use a vlookup either from another worksheet as the info is moved between workbooks and sent out to external parties which would break the link.

The code is as follows: SOP0001, SOP0002, SOP0003… however when it gets to 10 we drop a zero eg SOP0010, SOP0011, SOP0012… then drop another at 100 eg SOP0100, SOP0101…. And because there is this drop of the zero manually it doesn’t recognise a constant formula so won't autofill a new row.

This spreadsheet is to be set up for someone with MINIMUM excel experience so if I can get it to automate and lock the formula there should be no issue of accidental duplication of IDs which has had happened more than once already. Using Macros or VBA codes is a big no no so any help would be greatly appreciated!

Additional Info - The columns may be sorted by A-Z and so the ID needs to stick with the data; the formula provided so far hasn't worked. I have attached a sample of data. It is the first column I need to automate - the Project ID. Thank you

[AliGW]

You can do this easily with custom number formatting.

in A2 (assuming that A1 contains a blank or text):

=IFERROR(A1+1,1)

Custom formatting:

"SOP"0000

An alternative without custom formatting would be this:

=IFERROR("SOP"&TEXT((A1+1),"0000"),"SOP"&TEXT(1,"0000"))

The disadvantage of this option is that the ID is seen as text, whereas the first option is a number.

[mbowick]

I had tried this and it hadn't worked! I just tried again and still wouldn't work but then converted the data back to range then back to table and now it's working!! thank you so much, I felt I was going round in circles with this.

[AliGW]

Glad to have helped.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[mbowick]

Hi, I found a problem with the solution sorry! when I sort the table ID stays in the row when I need it to go with the original line. I know you can use $ to absolute but when I do that the formula no longer copies correctly when I add a new line to my table, suggestions?

[AliGW]

Please provide a workbook showing the new issue.

Bear in mind that we provide solutions based on the information we are given: you did not say that this needed to work in a table that might be sorted, nor did you provide sample data that might have suggested this.

[mbowick]

I think I have attached it now?

[AliGW]

Right.

You have two options: change your approach slightly or use VBA. Which would you prefer?

[mbowick]

it is for other people who have zero excel skills so would like to avoid VBA if possible as they wouldn't know how to fix it or change in future and I may not be here

[AliGW]

In that case you should be using a database that requires no user manipulation.

A spreadsheet is not the way to go in your case. You would have to go with a VBA solution to make it idiot-proof (sorry, but that's what you are saying!).

You have shared with us only a pinhole peep into the real thing, and I fear this thread could go on and on as you add in more and more requirements.

I am going to respectfully withdraw from this thread. Sorry.

[Phuocam]

Try this in A2:

=COUNTA($B$2:B2)

Or:

=ROWS($A$2:A2)

[AliGW]

How is this going to resolve the sorting issue?

[samba_ravi]

try below formula in A2 and use data validation
Please find the attached file.

[AliGW]

That doesn't wolve the sorting issue either!

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	A	B
1	ID	NAME
2	SOP0001	Bob Arms
3	SOP0002	Bob Arms
4	SOP0003	John Smith
5	SOP0004	John Smith
6	SOP0005	John Smith
7	SOP0006	John Smith
8	SOP0007	John Smith
9	SOP0008	John Smith
10	SOP0009	Michael Hanks
11	SOP0010	Michael Hanks
12	SOP0011
13	SOP0012

Sheet: Sheet1

[samba_ravi]

Try below formula in A2, the number will not change even after sorting the data
=ROW(Sheet1!$A2)-ROW(Table1[[#Headers],[ID]])

[mbowick]

IT WORKED! thank you so much I use microsoft office exl app 365 if anyone uses this solution