Finding data maximums

Hi Steven, thanks for your query. There are many ways of doing this, here is a rather rough and ready bit of code to do it.

First of all, you'll need headings on your data to do it this way. I put your data onto a sheet called "mydata" with the headings "A" and "B".

I used the advanced filter to pull out the unique values in column A

I brought across the second heading.

I use a series of loops and conditional test to pull out the largest values.

Finally, I attach the whole thing to a command button on the sheet. Here comes the code:

***************

Option Explicit

Private Sub CommandButton1_Click()

Dim introwcount As Integer
Dim listrowcount As Integer
Dim myvalue As Integer
Dim mystorednumber As Integer

myvalue = 0

Columns("A:A").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("D1"), Unique:=True

Sheets("mydata").Range("e1") = Sheets("mydata").Range("b1")

For introwcount = 2 To Sheets("mydata").Range("d1").CurrentRegion.Rows.Count

mystorednumber = Sheets("mydata").Cells(introwcount, 4).Value

For listrowcount = 2 To Sheets("mydata").Range("a1").CurrentRegion.Rows.Count

If mystorednumber = Sheets("mydata").Cells(listrowcount, 1).Value Then

If Sheets("mydata").Cells(listrowcount, 2).Value > myvalue Then

myvalue = Sheets("mydata").Cells(listrowcount, 2).Value

End If


End If

Next listrowcount

Sheets("mydata").Cells(introwcount, 5).Value = myvalue

myvalue = 0

Next introwcount

End Sub

***************

Hope this helps,

Anthony