Google Sheets - Generating a Sequential list based on previous values

Hi,

I am trying to generate a unique ID in Google Sheets.

The structure of the unique ID is the following:

2x Letter values (A-Z)+ 2x Digit values (18,19 or 20) + 2x Alphanumeric (A1,A2...Z9).

With the above, an example unique ID will look like this: GB18A1

I would like the last 2 alphanumeric characters to be generated in sequence (A1, A2.. A9, B1, B2.. B9...) and taking into account previous 4 characters, since the sequence is dependent on the first 4 characters and does not run independently.

So, when someone is creating a new ID with first 4 characters being LD18, sequence will start from A1. Next time some creates an ID with LD18 as first 4 characters, A2 will be spit out. But if first 4 characters are changed, sequence will have to start from A1 again.

I am unable to post a link but will do so if it's required (my forum access rights do not allow me to post links yet)

Originally Posted by lalalalalala

... (my forum access rights do not allow me to post links yet) ...

But you can attach a workbook - see the yellow banner at the top of the screen for details of how to do this.

Hope this helps.

Pete

Thank you Pete. Attached the sheet.

I'm just about to log out now and go to bed, as it is past 2:15 am here. I'm out for most of tomorrow as well, so I'll have to pick this up in the evening, unless someone else can chip in.

Pete

I managed to get alphanumeric sequence working but independent of previous values. I am still looking for a solution that would run the sequence based on past values.

example:

AB12A1
AB12A2
AB12A3
QB13A1
QB13A2

It would help if you had a few more rows of data than one, and you can show how you expect the ID to change each time.

Pete

you're right. Here comes the updated sheet with a few more examples

See if this does what you want. Put this formula in D2:

=CHAR(INT(COUNTIF(E$1:E1,A2&B2&C2&"*")/9)+65)&MOD(COUNTIF(E$1:E1,A2&B2&C2&"*"),9)+1

then copy down. Note that it depends on column E also being completed, so you should copy both formulae down further if you add some more data.

Hope this helps.

Pete

This is impressive, it worked. Thank you Pete!

You're welcome - glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete