• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 681
  • Last Modified:

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
0
Daniele Questiaux
Asked:
Daniele Questiaux
  • 3
  • 3
1 Solution
 
tdlewisCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
Daniele QuestiauxResearch associateAuthor Commented:
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
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Daniele QuestiauxResearch associateAuthor Commented:
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

Open in new window


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
 
tdlewisCommented:
@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

Open in new window

0
 
Daniele QuestiauxResearch associateAuthor Commented:
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
 
tdlewisCommented:
You're welcome.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now