Compare version numbers and return the highest value

suttungur · #1 (**permalink**) 10-13-11, 18:14

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.

myle · #2 (**permalink**) 10-15-11, 03:35

Why not just remove the .s and make it a number

Colin Legg · #3 (**permalink**) 10-17-11, 10:13

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)

Internet Services

Web Development

Digital Marketing

Consumer Tech