How to Update filter criteria from list and storing the query to another worksheet

[changeable]

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 macroi 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.
(e-mail address removed)


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

 

[Tom Ogilvy]

It would be easier to make three dummy columns adjacent to your original
data. Each column would be used to categorize each row against one of the
three criteria. The time and critical segment might require lookup tables.
Once the formulas in these columns correctly categorize the rows, then you
can use a pivot table.