How to get min value in a group?

Question

Example:

ID  Value   MAX
Group1  2   6
Group1  4   6
Group1  6   6
Group2  1   3
Group2  3   3
Group3  7   8
Group3  4   8
Group3  2   8
Group3  8   8
Group4  1   3
Group4  2   3
Group4  3   3
Group5  7   7

The column 'MAX' has the results I want.

My two part question is:

(1) How can I get the values for the 'Max' column?

I am currently using a pivot table but users complain it is too slow and can make Excel non-responsive.

I tried to use array functions with the formula like this:

=MAX(IF($A$9:$A$21=A12,$B$9:$B$21))

This doesn't stay current and I need some mechanisms to refresh the data. Users said they don't want yet-another-button to refresh the data.

(2) Assuming there is a formula to solve the above, my Value column is a date which could be empty, and my requirement is also to get the minimum date in the group, ignoring any blanks.

Solution 1

A few things... my first issue was that my existing spreadsheet was set to 'manual calculation' instead of 'automatic calculation'. (under menu Formulas | Calculation Options).

Here is some sample code I use to add calculate the min date based on a 'grouping' from another column. (NOTE: my spreadsheet has about 1500 rows, and I do notice a slowdown when making changes to cells and having the formulas get updated)

Sub AddFormulaToCalculateEarliestRevisedDate()
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    Dim identifierColumn As String
    Dim identierRow As String
    Dim identifierRange As String
    Dim valueRange As String
    Dim formulaColumn As String
    Dim formulaRange As String

    Dim myIdentifierRange As Range
    Dim myFormulaRange As Range
    Dim lastRow As String
    lastRow = ActiveSheet.Range("C5000").End(xlUp).Row

    identifierColumn = "B"
    identifierRange = "B6:B" & lastRow
    valueRange = "AP6:AP" & lastRow
    formulaColumn = "CZ"
    formulaRange = "CZ6:CZ" & lastRow

    Set myIdentifierRange = ActiveSheet.Range(identifierRange)
    Set myFormulaRange = ActiveSheet.Range(formulaRange)

    ' delete any existing any array formulas first! otherwise, get error
    myFormulaRange.ClearContents
    myFormulaRange.NumberFormat = "m/d/yyyy;;" ' notice the ;; to handle zero dates 1/0/1900 to be blank

    ' loop through each row and set the array formula
    Dim identifierCell As String
    Dim arrayFormula As String
    Dim r As Range
    For Each r In myIdentifierRange.Rows

        ' example: arrayFormula = {=MIN(IF($B$6:$B$5000=B6,$AP$6:$AP$5000))}
        identifierCell = identifierColumn & r.Row
        arrayFormula = "MAX(IF(" & identifierRange & "=" & identifierCell & "," & valueRange & "))"

        Range(formulaColumn & r.Row).FormulaArray = "=" & arrayFormula
    Next


    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Solution 2

In C2 enter the array formula:

=MAX(IF(A:A=A2,B:B))  

and copy down.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.