excel - 需要通过文本和数字组合找到最大值

Question

我在一列excel表中有一些数据。（文本-数字组合）喜欢：

AAA-32 
BBB-54
AAA-221
CCC-05
DDD-212

文本总是 3 个字符长 & 后面总是跟着一个“ - ”，然后是一个数字。

我需要找到 AAA、BBB、CCC ..& 其他具有最大数量的文本的最大值。

AAA 的最大值是“AAA-221”，同样需要其他字符（BBB、CCC ..）

如何使用 VBA 实现它？

`Data is present in column A
'lRow = Last Row
`tchar contains no. of unique characters, here 4 (AAA,BBB,CCC,DDD)

Dim i as Integer, tchar as Integer, tArray() as String, rng as Range

'Copying unique characters from col A into col Z. (Ignore Cell A1, Z1)
Range("A2:A" & lRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z2"), Unique:=True

'To get tchar
Range("Z2").Select
Range(Selection, Selection.End(xlDown)).Select
tchar = Selection.Rows.Count

'Range with unique characters
Set rng = Range("Z2", "Z" & lRow)

'Re-Define array
ReDim tArray(tchar)

'Getting unique characters into array
For i = 1 To tchar
tArray(i) = rng.Item(i).Value
Next i

'Separated Text & Numbers  into col B & C
'Col B has text (AAA,BBB...)
'Col C has numbers (32, 54, 221 ..)

'Now to use tArray() to traverse in col B & get max value from col C

'**I am stuck here**

标签： excelvbatextnumbers

Answer 1

该任务看起来像是使用 Dictionary 对象的经典案例

Option Explicit

Sub SelectMax()
    Dim Key As String, Value As Long, x As Variant, a As Variant, cnt As Long
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    
    With ThisWorkbook.Worksheets("Sheet1")
        For Each x In Intersect(.Columns("A"), .UsedRange)
            a = Split(x, "-")
            
            Key = a(0): Value = Val(a(1))
            
            If dict.exists(Key) Then
                If Value > dict(Key) Then dict(Key) = Value
            Else
                dict.Add Key, Value
            End If
        Next
        
        ' output the result
        Range("B1") = "Max" 'header
        cnt = 1
        For Each x In dict.Keys
            Range("B1").Offset(cnt) = x & "-" & dict(x)
            cnt = cnt + 1
        Next
    End With
End Sub