Find lowest value within group

[heidenman]

I need to find the lowest value in a specific column when match from a "base cell" is apparent in another.

For example:

In column 1 I have groups.

a1 is apples
b1 is pears
c1 is bananas
d1 is apples
e1 is pears
f1 is bananas

In column 2 I have prices

a2 is 10,00
b2 is 15,00
c2 is 20,00
d2 is 12,00
e2 is 17,00
f2 is 22,00

In column 10 I have an input box where you enter fruit.
a10 is apples

I want the output to be the cheapest priced apple.


Anyone? Thanks!!

[Phil_V]

I think you might have column and row references muddled in your post, columns are 'lettered' and rows are numbered.

Anyway, here is the test data I used, (the titles are in A1 and B1 respectively):

Item	Price
apples	10
pears	15
bananas	20
apples	12
pears	17
bananas	22

E1 is the INPUT cell where you enter the name of fruit you are interested in.
E2 is the OUTPUT cell where the min price will be.

This code needs to go into the 'Sheet' area of the code.
Right click on the tab of the worksheet your data is on, and 'View Code'. Then paste the following into the editor.

Private Sub Worksheet_Change(ByVal Target As Range)
Const IN_CELL As String = "E1"
Const OUT_CELL As String = "E2"

' Activate only if the input cell is changed
If Target.Count > 1 Or Intersect(Target, Range(IN_CELL)) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With Worksheets(Target.Parent.Name)
    .AutoFilterMode = False ' Remove any old filter
    .Cells(1, 1).AutoFilter field:=1, Criteria1:=Target.Value ' filter on the INPUT cell
    Application.EnableEvents = False
    ' Set the output cell to the minimum price
    .Range(OUT_CELL).Value = WorksheetFunction.Min(.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible))
    Application.EnableEvents = True
    .AutoFilterMode = False ' Remove the filter again
End With
Application.ScreenUpdating = True
End Sub

[daddylonglegs]

You can use an "array formula" like this

=MIN(IF(A1:A10=C1,B1:B10))

confirmed with CTRL+SHIFT+ENTER

where C1 contains the fruit for which you want the lowest price

[Phil_V]

I've GOT to get my head around array formulas! I think VBA to much