求实现n*2数组首列唯一值对应第二列数值求和的VBA函数

Got it, let's build that VBA function you need—it'll take your n×2 input array, pull out unique values from the first column, and calculate the sum of matching values in the second column. Here's a reliable implementation using a Dictionary (it's perfect for tracking unique keys and their running totals):

Function unqfiladv(inputArr As Variant) As Variant
    Dim dict As Object
    Dim i As Long
    Dim outputArr() As Variant
    Dim key As Variant
    Dim outputRow As Long
    
    ' Initialize the Dictionary to track unique items and sums
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' Loop through the input array
    For i = LBound(inputArr, 1) To UBound(inputArr, 1)
        ' Check if the item already exists in the Dictionary
        If dict.Exists(inputArr(i, 0)) Then
            ' If it does, add the current value to the existing sum
            dict(inputArr(i, 0)) = dict(inputArr(i, 0)) + inputArr(i, 1)
        Else
            ' If not, add the item to the Dictionary with its initial value
            dict.Add inputArr(i, 0), inputArr(i, 1)
        End If
    Next i
    
    ' Resize the output array to match the number of unique items
    ReDim outputArr(0 To dict.Count - 1, 0 To 1)
    
    ' Populate the output array with unique keys and their sums
    outputRow = 0
    For Each key In dict.Keys
        outputArr(outputRow, 0) = key
        outputArr(outputRow, 1) = dict(key)
        outputRow = outputRow + 1
    Next key
    
    ' Return the final summarized array
    unqfiladv = outputArr
End Function

How this works: #

• We use a Scripting.Dictionary (a handy tool for tracking unique keys and their associated values) because it automatically enforces unique entries—perfect for capturing the distinct values from your first column.
• As we loop through the input array, we either add a new entry to the Dictionary (for a first-time value) or add the current number to the existing sum (for repeat values).
• Finally, we convert the Dictionary's keys and values into a properly sized n×2 output array that matches your expected structure.

Testing with your sample code: #

When you run your test subroutine, the tempar array will hold exactly the results you're looking for:

• banana | 15
• apple | 8
• cucumber | 12
• a | 3

Just note that the Scripting.Dictionary is enabled by default in most Excel VBA environments, but if you run into issues, you can add a reference to "Microsoft Scripting Runtime" via Tools > References in the VBA editor.

内容的提问来源于stack exchange，提问作者user7181718