Max-function with alphanumeric values?!?

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Hi

I have a column with alphanumeric data. The max function works only with
numeric values. How to calculate the max-value of alphanumeric values?

Tom
 
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom
 
What is the max in your sample data - does 02 take precedence over any 01 in
the first two places or is the max the highest of the last 3 digits.

Can a helper column be used next to this column?

--
Regards,
Tom Ogilvy

Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom
 
Hi Tom, Hmmm...we need more information. Which one is MAX in your example.

02 of 02/001 is MAX? or 003 of 01/003?

Anyway try something like this...(the Code-function must fails again.)

'-----------------CODE -------------------------------------------------

Sub Test()
MsgBox CustomMax([A1:A4], 1) 'Take value of the Left side
MsgBox CustomMax([A1:A4], 2) 'Take value of the Right side
End Sub


Function CustomMax(ByVal Target As Range, ByVal lngNum As Long) As String
Dim buf1, buf2()
Dim ret
Dim i As Long
buf1 = Target.Value
ReDim buf2(LBound(buf1) To UBound(buf1))
For i = LBound(buf2) To UBound(buf2)
buf2(i) = Val(Split(buf1(i, 1), "/")(lngNum - 1))
Next
ret = Application.Match(Application.WorksheetFunction.Max(buf2), buf2,
0)
If Not IsError(ret) Then
CustomMax = buf1(ret, 1)
Else
CustomMax = "Error"
End If
End Function

'----------------------------------------------------------
Coolio

I suppose that the Code-function fails... I have numbers like this:

01/001
01/002
01/003
02/001

Tom
 
the max-value of the the following entries is 02/001

01/001
01/002
01/003
02/001

I can solve this with a customized function, but I can't believe that this
can't be solved with a worksheet function.

Tom
 
you could use a helper column (assume values in A, helper column B)

=SUMPRODUCT(CODE(MID(A1,{1,2,3,4,5,6},1)),10^{5,4,3,2,1,0})

Drag fill down column B.

=Index(A1:A4,Match(Max(B1:B4),B1:B4,0),1)

Basically, the concept of max for strings is not defined. So believe it.
 
Back
Top