Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

finding data maximums

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Finding data maximums

Finding data maximums

ResolvedVersion 2003

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

Sun 25 Dec 2011: Automatically marked as resolved.

 

Training courses

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.

Excel tip:

Use the Format Painter to copy formatting more than once in Excel

The format painter tool provides a quick and easy way to copy formatting from one cell to another in Word.

The only problem is that if you click the Format Painter once to turn it on, you can only click and drag over a single cell or adjacent range of cells; then the Format Painter turns itself off automatically.

If you want to copy formatting to cells or groups of cells that are not adjacent to each other, double-click the Format Painter - this way you will be able to copy formatting to multiple cells.

When you have finished using Format Painter, press the Esc key or click on the Format Painter button once to turn it off.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.09 secs.