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

Posted on 2012-09-03
Last Modified: 2012-09-04
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!
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.
    LVL 31

    Expert Comment

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


    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.

    Rob H

    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!

    Author Comment

    I am stuck!

    I created a dynamic range name for the ID number column ( IDNUMB), then wrote the code
    Application.ScreenUpdating = False
        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!
    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:
        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


    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.
    LVL 10

    Expert Comment

    You're welcome.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    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…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now