Steven has attended:
Excel VBA Intro Intermediate course
Finding data maximums
if i have a list of data with values in column 1 (some of which may be the same number), and the related parameter in column b, how can i extract the maximum value of column b for each value in column a?
e.g.
a b
1 3
2 5
1 6
1 8
2 8
3 1
3 8
1 9
i would need a table producing
a b
1 9
2 8
3 8
RE: 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