PDA

View Full Version : help with autofilter



rama4672
08-06-2004, 06:36 AM
What i have is a spreadsheet with store delivery times on them,we have an arrival time and depart time,then it gives us a turnaround time ie how long we spent unloading at the store.
Now what i want to do is to be able to filter out the 3 stores which took the longest to turn around.
But we also exclude the first hour as they get an hour to turn us around, and we need to work out how much over the hour each store took.

Thanks for any help

Ian

Jacob Hilderbrand
08-06-2004, 07:00 AM
You can use the rank function to rank the times. Something like

=RANK(A2,$A$2:$A$23)<4

to return TRUE or FALSE

rama4672
08-06-2004, 01:02 PM
thanks for that drj
But looking at my question again i probably didn't explain what i wanted.
It is a macro that i need to look for just the top 3 worst turnarounds.
then once it has found them copy the rows that cotain the 3 worst and paste them to another part of the sheet.

thanks

Ian

Jacob Hilderbrand
08-06-2004, 03:50 PM
Try this: Also, see attached example.



Option Explicit
Sub Macro1()

Application.ScreenUpdating = False

Columns("B:B").Insert Shift:=xlToRight
Range("B2").Value = "=RANK(A2,$A$2:$A$23)<4"
Range("B2").AutoFill Destination:=Range("B2:B" & Range("A65536").End(xlUp).Row)
Range("A1:B1").AutoFilter
Range("A1:B1").AutoFilter Field:=2, Criteria1:="TRUE"
Range("A1:A" & Range("A65536").End(xlUp).Row).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Range("A1:B1").AutoFilter
Selection.Copy
Range("E1").Select
ActiveSheet.Paste
Range("B:B").Delete

Application.ScreenUpdating = True
End Sub