finding the next ID number from a unsorted list of ID numbers with prefixes
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?
Adding a help column in the table could be added.
Thanks for the help!
Danièle
example-table.xlsx
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?
Adding a help column in the table could be added.
Thanks for the help!
Danièle
example-table.xlsx
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
=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
ASKER
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
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
ASKER
I am stuck!
I created a dynamic range name for the ID number column ( IDNUMB), then wrote the code
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
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 SubThere 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
You're welcome.
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.