asked on

How to find the min value for each product type

Given 2 columns (A) = Product Type (B) = Sales ($)
with multiple rows (sales amount) for each Product Type
without using a pivot table, is there a way to add a column C which contains the min Sales for that product type?

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

Here's a macro

Sub FindMinimums()
Dim lngProducts As Long
Dim lngRow As Long
Dim curMin As Currency

Application.ScreenUpdating = False

With ActiveSheet
'        .Range("A1:E" & lngLastRowE).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("U1"), Unique:=True
    .UsedRange.AutoFilter
    .Range("C1:D" & .UsedRange.Rows.Count).ClearContents
    .Range("A1:A" & .UsedRange.Rows.Count).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("C1"), Unique:=True
    .Range("C1") = "Products"
    .Range("D1") = "Min Sales"
    .Columns("A:A").Select
    For lngProducts = 2 To .Range("C1048576").End(xlUp).Row
        Selection.AutoFilter
        curMin = 999999999
        ActiveSheet.Range("A1:A" & .UsedRange.Rows.Count).AutoFilter Field:=1, Criteria1:=.Cells(lngProducts, "C")
        For lngRow = 2 To .UsedRange.Rows.Count
            If .Rows(lngRow).Hidden = False Then
                If .Cells(lngRow, "B") < curMin Then
                    curMin = .Cells(lngRow, "B")
                End If
            End If
        Next
        .Cells(lngProducts, "D") = curMin
    Next
    .UsedRange.AutoFilter
End With

Application.ScreenUpdating = True
End Sub

Open in new window

endurance

ASKER

Thanks