Sequential Numbering Macro

I have a large sheet (currently some 5,000 rows and growing) where each row is allocated a Unique Reference, however that unique reference is based on two criteria, 'Region' and 'Type'.

There are four 'Regions' and three 'Types' across the whole sheet (see attched sample).

Because of the ever increasing number of entries and the fact that the sheet may be sorted so the unique references won't always appear in sequential order, I am looking to try and find a way for the unique refrence for 'new entires' to the sheet to be generated automatically, based on entires in other columns. The sheet structure is relatively simple, with 'Region' shown in one column and 'Type' in another (again, see attached sample).

The unique references adopt this structure - 1st letter of the region (N, S, E or W for North, South, East or West), followed by 1415, followed by the first 4 characters from the 'Type', (REGI, NATI or COUN for Regional, National or County) followed by a sequential 5 digit number 00001, 00002, 00003 etc.

resulting in for example N1415REGI00001 or W1415COUN00012 and so on.

Because there are thousands of entries, I need an automatic way for the unique reference to be generated, ideally once both the 'Region' and 'Type' fields are populated, so the macro (or whatever method works best) will automatically determine the previous highest number for the relevant series and automatically add the next number for the new entry, based on the above criteria.

I don't know if this is possible with a macro or whether there is an easier formulaic way to achieve this?

Many thanks

if you want the formula, put this in A2

Please Login or Register  to view this content.

@SLX, thanks for the formulaic solution. Thinking about it, I think the macro route is propably neater... but I'm going to experiment with both...

Hi Kaper,

This looks like a real neat and elegant solution... a couple of questions, when Holger say's he'd prefer 'Enable Events' over 'Screenupdating', what does this mean? Secondly, I've realsied the URN column will need to be locked to prevent people editing the entries, but when the sheet is protected, I get an error, is there anyway around this?

Many thanks

One further thought... I noticed that once the unique reference has been generated, if the user were to go back and edit say the region, changing it from say North to East, the unique reference doesn't update. I guess this is tricky because any number of new unique references could have been generated in the meantime!

Is there any way to accommodate this scenario?

Many thanks

Hi Kaper,

This looks really good, I'll play around with it and see how it works...

Many thanks for taking the time to help, it is greatly appreciated...

Hi Kaper, this works perfectly, thank you...

If, in another column I wanted to add the record number, in the format 00001, 00002, 00003 etc could this be done by adding to your code?

Many thanks

Hi Kaper,

Sorry, I don't think I explained the new column very well. Column D will be totally independent to the Unique References in Column A. Column D will be the record number, so if there are say 29 records in the sheet already and the user adds a new one, this will automatically be 00030, then the next record will be 00031, so you might have for example:

S1415REGI00005 South Regional 00030
S1415NATI00001 South National 00031
N1415COUNT00002 North County 00032


Bearing in mind that the records may not be in order if the user has sorted by one of the columns. I hope that makes sense...

Many thanks for your help

Hi Kaper,

That works perfectly, thank you...

Can I ask the logic of running the additional macro? If I already have existing numbers in column D, this appears to remove these and renumber the existing rows starting from the next highest number, so if I say already have numbers 00001 to 00100 in place, when I run the additional macro, my existing numbers are renumbered starting at 00101 through to 00200? I would need to keep any existing numbers in place, so I'm trying to understand the reason for running this additional macro?

Many thanks

Okay, I'll have a play with it and let you know how I get on...

Many thanks for taking the time to help me with this, it is very much appreciated...

Hi Kaper,

Apologies, I've added your code to my main sheet and realised that the number added in Column D, relates to the number of instances of the reference in column B, so I think I didn't explain how I need column D number to work so well.

The number in column D is literally counting the total number of row entries, so effectively =ROW(), rather than the number of entries for a particular set of unique references, so for each new entry made, the number should simply increase by one if that makes sense.

There will be instances where the user will insert a new row in the middle of the data, so there may be entries in rows 1 to 1,000, numbered 00001 to 01000, the user inserts a new row at row 500, so the new number in column D for this new entry in row 500 would be 01001 whereas the unique reference may be something like L1314CUST00042.

How would you adapt your code to achieve this?

Many thanks

Hi Kaper,

It is working correctly on the attachment, but not on my main sheet, so I must have made an error when translating the column references. I will take another look and let you know if I am still having a problem...

Thank you once again...

Hi Kaper,

It is working correctly on the attachment, but not on my main sheet, so I must have made an error when translating the column references. I will take another look and let you know if I am still having a problem...

Thank you once again...

Hi Kaper,

I figured out my mistake when translating the column refs, so all working now...

Many thanks for your patience and taking the time to help with this, it is very much appreciated...

Hi Kaper,

Not sure if this needs to appear as a new thread, but I need to run your macro on a protected sheet, so I tried putting teh code between ''Sheets("Data").Unprotect Password:="password" and 'Sheets("Data").Protect Password:="password", but when selecting an option from the first column, the sheet unlocks, which is fine, but this allows the user to enter things in unlocked cells which I don't want.

The behaviour I need is that when the user selects an option from the fist dropdown, the sheet unlocks then locks and this is then repeated when the select an option from the second dropdown but at all other times the sheet reamins locked.

Do I need to include ''Sheets("Data").Unprotect Password:="password" and 'Sheets("Data").Protect Password:="password" twice in the code and if so where about should they sit exactly?

Please Login or Register  to view this content.