10-13-11, 18:14 #1Registered User
- Join Date
- Oct 2011
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.1.0 << highest of the three
Can also be:
1.11.0 << lowest
1.2.0 << 2nd highest
2.00.0 << highest
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.
10-15-11, 03:35 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
Why not just remove the .s and make it a numberhope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON
10-17-11, 10:13 #3Registered User
- Join Date
- Sep 2008
- London, UK
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 >.
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
Last edited by Colin Legg; 10-17-11 at 10:30.Colin
RAD Excel Blog