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

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.

--
Regards,
Tom Ogilvy


"changeable" wrote in message
...

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 and
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 following
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 group
of data that fall in:
certain " Day " (monday....sunday) and within certain " time " (7:00:00
to 7:05:00, 7:05:00 to 7:10:00, up to 20:55:00 to 21:00:00) and satisfy
certain criteria " segment " (3.137263<=Y<=3.138149.and so on....)

I know this can be done by using the "auto filtering", however it is
too tedious as i have to repeatly select those criteria one by one
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 data
that i have (around 30-40k). When i try a small portion of my data using
pivot tables, instead of select "range of criteria" i.e. (say time from
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 X
only, 3) involve satisfying criteria X and Y. This had complicated my
filtering process.


At first, i thought my question will need lots of FOR loop and IF, but
i think it can be done by just using the existing filter function in
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, and
then for segment, k
3) The worksheet will show only the data that match the criterias, and
i have to copy the visible row, and the corresponding "SPEED" data to a
worksheet and get it's average and standard deviation.
4) return to step 2, but changing the k to k +1 (ie, another SEGMENT
CRITERIA)

however, i do not know how to write a syntax that keep updating the
"criteria" in step 2. Can someone help me, please? This is what i have
tried but then failed to finish the macro:(i am learning it, but it is
very urgent for me to solve the problem that i'm afraid i dont have
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