Finding the greater text value from several cells

[RKramkowski]

Hi, I know LARGE and MAX can be found to find the largest numerical value from several cells, but how about text?

I have several columns of software versions for devices and want to be able to find the larger of the list. So column A contains 11.2.34.5 and column B contains 14.3.44.5. They represent different versions of the same software and I want a function to return 14.3.44.5 in the above example. These are stored as text values, not numerical, due to the multiple decimals. Converting to numeric and looking in the leftmost delimited part (by the leftmost decimal) won't work because then I'll miss the difference between 11.2.34.5 and 11.2.34.15 where I want to return the 11.2.34.15 value.

This sounds like it ought to be simple but I've had no luck so far.

thanks,
Bob

[SamT]

This will require a not simple and very specific to your workbook VBA solution. Can you post a sanitized version of the workbook that we can view. Someone is sure to help you then.

[snb]

Did you try the result of a sorting procedure ?

[RKramkowski]

The whole spreadsheet is over 100,000 lines and about 40 columns. Most columns are lookups from other tabs or calculated. These in the example are obtained by using index/match and then I need to identify the one that is greater. I believe being able to select based on the ascii value of the text field should do it but ASC didn't work. And MAXA() is supposed to handle text but also doesn't work in this case.

For some reason, the system isn't letting me attach a file so here's a short excerpt:




Version A
Version B
<- Should Return:


12.1.4100.4126

12.1.4100.4126



11.0.7200.1147
11.0.7200.1147


12.1.4100.4126

12.1.4100.4126


12.1.4100.4126

12.1.4100.4126


12.1.4013.4013

12.1.4013.4013


12.1.4100.4126

12.1.4100.4126


12.1.4100.4126

12.1.4100.4126


12.1.4112.4156
12.1.4112.4156
12.1.4112.4156


12.1.4013.4013




12.1.4112.4156
12.1.4112.4156
12.1.4112.4156



11.0.7200.1147
11.0.7200.1147


12.1.4112.4156
12.1.4112.4156
12.1.4112.4156


11.0.7200.1147
12.1.4112.4156
12.1.4112.4156


12.1.4112.4156
11.0.7200.1147
12.1.4112.4156


12.1.4112.4156
11.0.7200.1147
12.1.4112.4156


12.1.4112.4156
11.0.7200.1147
12.1.4112.4156

[RKramkowski]

I figured it out... after sleeping on it, it was amazingly easy. =IF(A2>B2,A2,B2)

[SamT]

Insert this code in a standard Module

Option Explicit

Function MAXV(Ref1 As Range, Ref2 As Range) As String
'Compares the numerical portions of Program Version strings
' as held in two Excel Ranges and returns the newest.
'Works on dot seperated type version strings only. Ex: 27.1.01
'For help see:
'http://www.vbaexpress.com/forum/showthread.php?51288-Finding-the-greater-text-value-from-several-cells

Dim Version1 As Variant
Dim Version2 As Variant
Dim WsF As Object
Set WsF = Application.WorksheetFunction

Version1 = WsF.Substitute(Ref1.Text, ".", "")
Version2 = WsF.Substitute(Ref2.Text, ".", "")

If Version1 >= Version2 Then
MAXV = Ref1.Text
Else
MAXV = Ref2.Text
End If

Set WsF = Nothing
End Function
And copy this formula down the "Should Return" Column. Assumes that the Version A column is Column "A" and VerB Col is Column "B," adjust as needed.

=MAXV(A1,B1)

[snb]

in the codemodule of the sheet:


Sub M_snb()
[D1:D100] = [if((B1:B100>=A1:A100)*(B1:B100<>""),B1:B100,A1:A100)]
End Sub