Solved

# finding the next ID number from a unsorted list of ID numbers with prefixes

Posted on 2012-09-03
676 Views
Good Morning,
I have a table in excel with samples ID, provenance etc. They were each given a sample ID with an AD prefix and 5 digits, the first 2 based on the year, and the last three numbers growing from 1 to 999.
The samples come from different regions, or owners, and users like to keep samples from same user or provenance together so that the table numbering is a bit "haphazard" with the last entry in the table is not necessarily the last entry made, and therefore, not the largest number.
I would like to have the new ID number automatically generated in the first empty row at the bottom of the table so that when a new sample is entered it can automatically get its new number, and if the user wishes, the row can be cut and pasted within the table with samples from same location or customer, and the next ID number generated for the next sample.
I think that a macro would be easier as with a formula, the number generated might change if the row is moved to another spot.
I am adding a mini table as an example which will clarify what I am looking for?
Thanks for the help!
Danièle
example-table.xlsx
0
Question by:daniques

LVL 10

Expert Comment

When do you want the new value to be inserted? Here are some options that come to mind:
a) As soon as an ID number is used, the next ID number gets added to the end of the table.
b) When the user clicks a button, the next ID number gets.
c) When the user enters a value into "Sample name" with a blank value for ID number, the next number gets filled in.
0

LVL 31

Expert Comment

You can pull out the maximum value with a fairly simple array formula:

=MAX(VALUE(RIGHT(A4:A8,3)))

Confirm with Ctrl +Shift + Enter.

Assumes values in range A4  to A8 so will need expanding accordingly and assumes format AA123, ie last 3 characters are numeric.

Thanks
Rob H
0

Author Comment

Thanks TDlewis and Rob!
I had planned to try and write the code using the formula =large(right(A4:A8,3),1), but first had forgotten to put VALUE, and then the Ctrl +Shift+Enter. Now having a corrected formula, I should try to write the code...!

I would go with the easier option of the formula, but the file is used by several users who would prefer "automatic" number generation and that is why a macro on cell change would automate that, ensuring that the number is kept as value and cannot change if the user forgets to copy and save the answer as value.
The users are not general excel users but have been shown how to copy whole rows and insert them where they wish, and keep using that system. The table has now grown large enough to create confusion as there has been a few duplicate entries found that have been since corrected.
The users tend to reshuffle the rows to group same customer samples together or to group samples by location. I have tried to show them the use of filters, but as they use the table infrequently, they tend to forget and revert to their old system.
I think that a macro code starting when a sample name is entered would probably be the safest, and most automatic way of generating a new ID# . There is the possibility of the user changing his or her mind and erasing the sample name, but it would not constitute a major irreversible problem.

Thanks for all the help!
Danièle
0

Author Comment

I am stuck!

I created a dynamic range name for the ID number column ( IDNUMB), then wrote the code
``````Sub NUIDNUMBER()
Application.ScreenUpdating = False
ActiveSheet.Select
With Range("a" & Range("A" & Rows.Count).End(xlUp).Row)
.Offset(-1, 0).Select
Selection.FormulaArray = "= max(value(right("IDNUMB,5)))+1"
.Value = .Value
End With
Application.ScreenUpdating = True
End Sub
``````

There is a compilation error, but I cannot figure where I used the wrong syntax or what is wrong.
The result though would still only give me the next number for the ID, but I do not know how to concatenate the resulting value with "Ad" in front.
I am not spotting the error, and so cannot even start trying to correct it!

I would be grateful for any help for the coding!
Thanks!
Danièle
0

LVL 10

Accepted Solution

@daniques, if you create a formula like that, it would be dynamic and get updated every time the list changes. I think you want something like this:
``````Sub NUIDNUMBER()
v = 0
For i = 2 To ActiveSheet.Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
x = Val(Right(ActiveSheet.Cells(i, 1).Value, 5))
If v < x Then v = x
Next i
ActiveSheet.Range("A" & i).Value = "Ad" & (v + 1)
End Sub
``````
0

Author Closing Comment

Exactly what I was looking for, finds the highest numerical value in a list of IDs, adds the ID prefix "Ad", and value "highest value +1" for the next sample.
The code is very short but I had some difficulty understanding the logic of it!
Thanks for a coding lesson as well as resolving my problem.
Thanks!
Danièle
0

LVL 10

Expert Comment

You're welcome.
0

## Featured Post

### Suggested Solutions

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…