Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    1

    Unanswered: Compare version numbers and return the highest value

    I'm trying to compare version numbers of software applications and need to retrieve the highest value, in other words keep track what is the latest version at any given time sheet is updated. Each application has a single, dedicated row containing the version numbers in random order (it is also possible for some or all cells to contain the same version). Format of the version numbers:

    1.0.0
    1.0.1
    1.1.0 << highest of the three

    Can also be:
    1.11.0 << lowest
    1.2.0 << 2nd highest
    2.00.0 << highest

    Also:
    11.22.33
    11.3.1 << highest

    I have been trying to achieve this by using another thread as a base guide, but have not had too much success with it.

    It would be most appreciated, should someone be available to assist me with this problem.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    Why not just remove the .s and make it a number
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Using that versioning convention, the easiest way would be to write a VBA UDF. So that it considers 3 to be greater than 22, etc, you have to perform string comparisons rather than numeric comparisons. Not sure if you want that for the major part of the version too? If not, change the code below so it casts the strings to longs before comparing them with >.
    Code:
    Public Function GetLatestVersion(ByVal rngToCheck As Range) As Variant
     
        Const strDELIMITER As String = "."
     
        'version convention:  Major.Minor.Patch   eg  1.3.2
     
        Dim strMaxMajor As String
        Dim strMaxMinor As String
        Dim strMaxPatch As String
        Dim strVersion() As String
     
        Dim rngCell As Range
     
        On Error GoTo ErrorExit
     
        For Each rngCell In rngToCheck.Cells
     
            If Not IsEmpty(rngCell.Value2) Then
     
                strVersion = Split(rngCell.Value2, strDELIMITER)
     
                If strMaxMajor = vbNullString Then
                    If IsNumeric(strVersion(0)) Then
                        strMaxMajor = strVersion(0)
                        strMaxMinor = strVersion(1)
                        strMaxPatch = strVersion(2)
                    End If
                Else
     
                    If strVersion(0) > strMaxMajor Then
     
                            strMaxMajor = strVersion(0)
                            strMaxMinor = strVersion(1)
                            strMaxPatch = strVersion(2)
     
                    ElseIf strVersion(0) = strMaxMajor Then
     
                        If strVersion(1) > strMaxMinor Then
     
                            strMaxMajor = strVersion(0)
                            strMaxMinor = strVersion(1)
                            strMaxPatch = strVersion(2)
     
                        ElseIf strVersion(1) = strMaxMinor Then
     
                             If strVersion(2) > strMaxPatch Then
     
                                strMaxMajor = strVersion(0)
                                strMaxMinor = strVersion(1)
                                strMaxPatch = strVersion(2)
     
                            End If
     
                        End If
                    Else
     
                    End If
                End If
            End If
        Next rngCell
     
        GetLatestVersion = strMaxMajor & strDELIMITER & strMaxMinor & strDELIMITER & strMaxPatch
     
        Exit Function
     
    ErrorExit:
        GetLatestVersion = CVErr(xlErrNum)
    End Function
    Once that's in a standard code module, you can call it from your formulae like this:

    =GetLatestVersion(E3:E5)
    Last edited by Colin Legg; 10-17-11 at 11:30.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •