PDA

View Full Version : Find maximum and minimum values each day



fooj
05-07-2009, 09:12 AM
I have had a stack of data sent through to me with a series of values recorded hourly over 5 years.

I wanted to try write some code that would look at the values for each day and pick the min and max for that day and paste them into another sheet.

The current data format is as below

Date Time Value
05/06/2009 12:00 69
05/06/2009 01:00 71
05/06/2009 02:00 70

Does anyone have any ideas on how I could achieve this?

Thanks

Bob Phillips
05-07-2009, 09:56 AM
Public Sub ProcessData()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To LastRow

If IsError(Application.Match(CLng(.Cells(i, "A").Value), _
Worksheets("Sheet2").Columns(1), 0)) Then

NextRow = NextRow + 1
.Cells(i, "A").Copy Worksheets("Sheet2").Cells(NextRow, "A")
Worksheets("Sheet2").Cells(NextRow, "B").Value = .Evaluate( _
"MAX(IF(A2:A" & LastRow & "=" & CLng(.Cells(i, "A").Value) & _
", C2:C" & LastRow & "))")
End If
Next i

End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

fooj
05-08-2009, 05:31 AM
Thanks for the quick response.

This works brilliantly.

Thanks again