Hi, i am a newbies here, i have posted a question few days ago. However
i still cannot solve it. I apologies for reposting my question here an
my bad english.


i have some data which looks like these:
Time Day Y X Speed
6:12:21 Monday 3.137623456 101.6953814 57
6:12:27 Monday 3.136615051 101.6952216 71
6:12:32 Monday 3.135641022 101.6951024 75
6:12:37 Monday 3.134624022 101.6949792 75
6:12:42 Monday 3.133702706 101.6947838 75
6:12:47 Monday 3.132911451 101.6942716 71
6:12:53 Monday 3.132395216 101.6934099 61
. . . . .
. . . . .

and i need to do some filtering on it which is based on the followin
criteria:

Criteria DAY,i
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
Sunday

Criteria Time Interval, j
7:00:00 -7:05:00
7:05:00 -7:10:00
7:10:00 -7:15:00
7:15:00 -7:20:00
up to
20:55:00 -21:00:00

Criteria Segment, k
3.137263<=Y<=3.138149
3.136371<=Y<=3.137263
3.135472<=Y<=3.136371
3.134575<=Y<=3.135472
3.133716<=Y<=3.134575
3.13297<=Y<=3.133716
Y<=3.13297AND101.693463<=X
101.692597<=X<=101.693463
3.131821<=YANDX<=101.692597
3.13099<=Y<=3.131821
3.130125<=Y<=3.13099
3.129286<=Y<=3.130125
Y<=3.129286AND101.690364<=X
101.689658<=X<=101.690364
101.688943<=X<=101.689658
101.688238<=X<=101.688943
101.687504<=X<=101.688238
101.686695<=X<=101.687504
101.685863<=X<=101.686695
101.685014<=X<=101.685863
3.124935<=YANDX<=101.685014
3.124425<=Y<=3.124935
3.123897<=Y<=3.124425
3.123389<=Y<=3.123897
3.122874<=Y<=3.123389
3.121694<=Y<=3.122874
3.120896<=Y<=3.121694
3.120017<=Y<=3.120896
3.119114<=Y<=3.120017
3.118215<=Y<=3.119114
3.11732<=Y<=3.118215
3.116493<=Y<=3.11732
Y<=3.116493AND101.678503<=X
101.677729<=X<=101.678503
101.676881<=X<=101.677729
101.675997<=X<=101.676881


i.e. I need to have the average speed and standard deviation of a grou
of data that fall in:
certain " Day " (monday....sunday) and within certain " time " (7:00:0
to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisf
certain criteria " segment " (3.137263<=Y<=3.138149.and so on....)

I know this can be done by using the "auto filtering", however it i
too tedious as i have to repeatly select those criteria one by on
which may results to 7(days) x 169 (duration) x 127 (segment criteria
= 150241 trials!!!!

I have tried pivot table too, but it cannot support the numbers of dat
that i have (around 30-40k). When i try a small portion of my data usin
pivot tables, instead of select "range of criteria" i.e. (say time fro
7:00:00 to 7:05:00) it can only select "a particular criteria" i.e
(say, 7:01:30 or 7:02:01 and etc).

Besides, there are 3 types of criteria in the "segment" criteria. 1
involve satisfying criteria Y only, 2) involve satisfying criteria
only, 3) involve satisfying criteria X and Y. This had complicated m
filtering process.


At first, i thought my question will need lots of FOR loop and IF, bu
i think it can be done by just using the existing filter function i
Excel.

I try to make it this way:
1). Apply autofilter to the fields
2) Select the Criteria for Day, i , and then for Time Interval,j, an
then for segment, k
3) The worksheet will show only the data that match the criterias, an
i have to copy the visible row, and the corresponding "SPEED" data to
worksheet and get it's average and standard deviation.
4) return to step 2, but changing the k to k +1 (ie, another SEGMEN
CRITERIA)

however, i do not know how to write a syntax that keep updating th
"criteria" in step 2. Can someone help me, please? This is what i hav
tried but then failed to finish the macro:(i am learning it, but it i
very urgent for me to solve the problem that i'm afraid i dont hav
plenty of time to learn....)



code:
--------------------------------------------------------------------------------

Option Explicit

Sub Filter()
Dim vaDatabase As Variant 'Define Array
Dim rgRow As Range
Dim rgSpeed As Range
Dim rgLast As Range
Dim lLastRow As Range
Dim p As Integer
Dim dSpeed As Double
Dim SpeedCount As Integer
Dim wsNew As Worksheet

Range("A1:N38037").Name = "AVLData" 'Naming the Range
vaDatabase =
Workbooks("Aug_f1.xls").Worksheets("Aug_f1").Range ("AVLData ") 'assign
AVL value to vaDatabase
rgSpeed = Workbooks("Aug_f1.xls").Worksheets("Aug_f1").Range .wh

'Filter by Day,i
Range("A1:H38037").AutoFilter Field:=4, Criteria1:="=Monday"
'Filter by Time,j
Range("A1:H38037").AutoFilter Field:=3, Criteria1:="=7:00:00",
Operator:=xlAnd, Criteria2:="<7:05:00"
'Filter by Segment, k...dont know how to do that

Set wsNew = Worksheets.Add
Set rgLast = Range("H2").SpecialCells(xlCellTypeLastCell)
SpeedCount = 1

For Each rgRow In vaDatabase.Rows
If rgRow.EntireRow.Hidden = False Then
Set rgLast = Range("H2").SpecialCells(xlCellTypeLastCell)
lLastRow = rgLast.Row

Range("rgSpeed").Copy Destination:=wsNew.Range("A1")
SpeedCount = SpeedCount + 1
End If

End Sub

Please help me. The sample of my data, criteria list and output format
are attached.



+-------------------------------------------------------------------+
|Filename: datasample.zip |
|Download: http://www.excelforum.com/attachment.php?postid=2743 |
+-------------------------------------------------------------------+

--
changeable
------------------------------------------------------------------------
changeable's Profile: http://www.excelforum.com/member.php...o&userid=15714
View this thread: http://www.excelforum.com/showthread...hreadid=274085