API reference · Arrays

GroupBy

Group rows by one key column and compute one aggregate from another column.

FunctionArrays→VariantSource L890

Signature#

Function GroupBy(ByVal vArr As Variant, ByVal lIndexKey As Long, ByVal lIndexGroup As Long, ByVal eGroupType As GroupType) As Variant

Parameters#

Parameter	VBA type	Default	Description
`vArr`	`Variant`ByVal	Required	Allocated 2D data matrix. Non-array and non-2D inputs are returned unchanged with a diagnostic in the Immediate window.
`lIndexKey`	`Long`ByVal	Required	Actual column subscript containing grouping keys. Keys use binary dictionary comparison and are kept in first-appearance order.
`lIndexGroup`	`Long`ByVal	Required	Actual column subscript containing values to aggregate. This column is validated even for eCount.
`eGroupType`	`GroupType`ByVal	Required	GroupType.eSum=1, eMean=2, eCount=3, eMin=4, eMax=5, eStDev=6. These eMin/eMax values differ from Finance.ExtremumType's 0 and 1.

Returns#

Variant containing a 2D matrix bounded (1 To groupCount, 1 To 2): column 1 is the key, column 2 the Double aggregate. Returns Empty for invalid column indices, an unsupported aggregate, or any Null/error-valued grouping key. Non-array or non-2D inputs are returned unchanged rather than Empty.

Example#

SEE THE TRANSFORMATIONSum amounts by category

Before

transactions2D · 4 × 2

Rows 1..4 · Columns 1..2

row / col	1	2
1	“Sales”	120
2	“Fees”	15
3	“Sales”	80
4	“Fees”	5

After

totals2D · 2 × 2

Rows 1..2 · Columns 1..2

row / col	1	2
1	“Sales”	200
2	“Fees”	20

Output columns are key and total. Groups follow first appearance, not alphabetical order.

Shaded headers are array indices, not data. Quoted cells are text. Values shown are derived from the source, not an Excel execution.

Sub ExampleGroupBy()
    Dim a As New Arrays
    Dim transactions As Variant, totals As Variant

    ReDim transactions(1 To 4, 1 To 2)
    transactions(1, 1) = "Sales": transactions(1, 2) = 120
    transactions(2, 1) = "Fees": transactions(2, 2) = 15
    transactions(3, 1) = "Sales": transactions(3, 2) = 80
    transactions(4, 1) = "Fees": transactions(4, 2) = 5

    ' GroupType.eSum = 1
    totals = a.GroupBy(transactions, 1, 2, 1)
    If IsArray(totals) Then
        Debug.Print totals(1, 1), totals(1, 2) ' Expected: Sales, 200
        Debug.Print totals(2, 1), totals(2, 2) ' Expected: Fees, 20
    End If
End Sub

Behavior and edge cases#

An early-bound Dictionary maps each key to its group index, with CompareMode=vbBinaryCompare. Text grouping is case-sensitive. An explicit key buffer preserves first appearance rather than sorting keys.
Empty keys form a dedicated group. A zero-length string follows the dictionary path and is distinct from Empty. Any Null or Excel error in the key column aborts the entire operation and returns Empty after printing a diagnostic; it is not just a skipped row.
Error and Null values in the aggregate column are skipped. Sum, mean, minimum, maximum and standard deviation also skip Empty and nonnumeric values; numeric strings accepted by IsNumeric are converted with CDbl.
eCount counts aggregate-column values that are neither Error, Null, Empty nor zero-length after CStr. It includes ordinary text and numeric values. It does not count all rows unconditionally and is not a numeric-only count.
eMean and eStDev update group statistics using Welford's recurrence. eStDev returns population standard deviation, dividing by the numeric count, and clamps negative roundoff variance to zero. A one-value group has standard deviation zero.
A key group is created even if all of its aggregate values are skipped. Such a group's aggregate is zero for every supported mode, including minimum, maximum and mean.
Other dictionary-key equivalence follows Scripting.Dictionary; there is no Filter-style CDbl/UCase normalization of keys. Unsupported keys can raise runtime errors. The caller's matrix is not modified.
The Dictionary declarations require the Microsoft Scripting Runtime reference. The actual GroupBy implementation calculates its own statistics and does not call the Finance class.

Performance#

Expected O(r + g) work for r input rows and g groups, assuming normal dictionary lookup cost. The implementation preallocates key and three-statistic buffers to r rows, so auxiliary storage is O(r), plus O(g) dictionary entries and O(g) output.

View implementation

Function GroupBy(ByVal vArr As Variant, ByVal lIndexKey As Long, ByVal lIndexGroup As Long, ByVal eGroupType As GroupType) As Variant
    'Groups a two-dimensional array and calculates one statistic per key in a single pass

    Dim oDictKey                As New Dictionary
    Dim vArr_Out                As Variant
    Dim vKeys()                 As Variant
    Dim vStats()                As Double
    Dim vKey                    As Variant
    Dim vValue                  As Variant
    Dim lRow                    As Long
    Dim lGroup                  As Long
    Dim lCount                  As Long
    Dim lBlank                  As Long
    Dim lRows                   As Long
    Dim dblValue                As Double
    Dim dblDelta                As Double
    Dim dblVariance             As Double

    If Not IsArray(vArr) Then
        Debug.Print ("Error GroupBy -> vArr is not an array")
        GroupBy = vArr
        Exit Function
    End If

    If Me.DimCounter(vArr) <> 2 Then
        Debug.Print ("Error GroupBy -> vArr must have two dimensions")
        GroupBy = vArr
        Exit Function
    End If

    If lIndexKey < LBound(vArr, 2) Or lIndexKey > UBound(vArr, 2) Then Exit Function
    If lIndexGroup < LBound(vArr, 2) Or lIndexGroup > UBound(vArr, 2) Then Exit Function

    Select Case eGroupType
        Case eSum, eMean, eCount, eMin, eMax, eStDev
        Case Else
            Exit Function
    End Select

    lRows = UBound(vArr, 1) - LBound(vArr, 1) + 1
    ReDim vKeys(1 To lRows)
    ReDim vStats(1 To lRows, 1 To 3)
    oDictKey.CompareMode = vbBinaryCompare

    For lRow = LBound(vArr, 1) To UBound(vArr, 1)
        vKey = vArr(lRow, lIndexKey)

        If IsError(vKey) Then
            Debug.Print ("Error GroupBy -> error value in grouping column")
            Exit Function
        End If
        If IsNull(vKey) Then
            Debug.Print ("Error GroupBy -> Null key")
            Exit Function
        End If

        If IsEmpty(vKey) Then
            If lBlank = 0 Then
                lCount = lCount + 1
                lBlank = lCount
                vKeys(lCount) = vKey
            End If
            lGroup = lBlank
        Else
            If Not oDictKey.Exists(vKey) Then
                lCount = lCount + 1
                oDictKey.Add vKey, lCount
                vKeys(lCount) = vKey
            End If
            lGroup = oDictKey(vKey)
        End If

        vValue = vArr(lRow, lIndexGroup)
        If Not IsError(vValue) Then
            If Not IsNull(vValue) Then
                If eGroupType = eCount Then
                    If Not IsEmpty(vValue) Then
                        If Len(CStr(vValue)) > 0 Then vStats(lGroup, 1) = vStats(lGroup, 1) + 1
                    End If
                ElseIf Not IsEmpty(vValue) And IsNumeric(vValue) Then
                    dblValue = CDbl(vValue)
                    Select Case eGroupType
                        Case eSum
                            vStats(lGroup, 2) = vStats(lGroup, 2) + dblValue
                        Case eMean, eStDev
                            vStats(lGroup, 1) = vStats(lGroup, 1) + 1
                            dblDelta = dblValue - vStats(lGroup, 2)
                            vStats(lGroup, 2) = vStats(lGroup, 2) + dblDelta / vStats(lGroup, 1)
                            If eGroupType = eStDev Then
                                vStats(lGroup, 3) = vStats(lGroup, 3) + dblDelta * (dblValue - vStats(lGroup, 2))
                            End If
                        Case eMin
                            If vStats(lGroup, 1) = 0 Then
                                vStats(lGroup, 2) = dblValue
                            ElseIf dblValue < vStats(lGroup, 2) Then
                                vStats(lGroup, 2) = dblValue
                            End If
                            vStats(lGroup, 1) = vStats(lGroup, 1) + 1
                        Case eMax
                            If vStats(lGroup, 1) = 0 Then
                                vStats(lGroup, 2) = dblValue
                            ElseIf dblValue > vStats(lGroup, 2) Then
                                vStats(lGroup, 2) = dblValue
                            End If
                            vStats(lGroup, 1) = vStats(lGroup, 1) + 1
                    End Select
                End If
            End If
        End If
    Next lRow

    ReDim vArr_Out(1 To lCount, 1 To 2)

    For lRow = 1 To lCount
        vArr_Out(lRow, 1) = vKeys(lRow)
        Select Case eGroupType
            Case eCount
                vArr_Out(lRow, 2) = vStats(lRow, 1)
            Case eStDev
                If vStats(lRow, 1) > 0 Then
                    dblVariance = vStats(lRow, 3) / vStats(lRow, 1)
                    If dblVariance < 0 Then dblVariance = 0
                    vArr_Out(lRow, 2) = Sqr(dblVariance)
                Else
                    vArr_Out(lRow, 2) = 0#
                End If
            Case Else
                vArr_Out(lRow, 2) = vStats(lRow, 2)
        End Select
    Next lRow

    GroupBy = vArr_Out
End Function