Excell automatic filters
Closed
aymandaoud
Posts
2
Registration date
Monday July 7, 2014
Status
Member
Last seen
July 8, 2014
-
Jul 7, 2014 at 12:03 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 9, 2014 at 04:20 AM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jul 9, 2014 at 04:20 AM
Related:
- Excell automatic filters
- Nvidia automatic driver detection tool - Guide
- Safe search filters - Guide
- Fullscreen javascript automatic - Guide
- Automatic redial samsung - Guide
- Automatic restart problem - Guide
2 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 8, 2014 at 06:41 AM
Jul 8, 2014 at 06:41 AM
better if a small extract of data is posted and then explained
suggestion
autofilter according to field and criteria
then use subtotal function (101,102 etc)
the function will be found only for visible data.
Evaluates hidden values Ignores hidden values Function
1 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
4 104 MAX()
5 105 MIN()
6 106 PRODUCT()
7 107 STDEV()
8 108 STDEVP()
9 109 SUM()
10 110 VAR()
11 111 VARP()
suggestion
autofilter according to field and criteria
then use subtotal function (101,102 etc)
the function will be found only for visible data.
Evaluates hidden values Ignores hidden values Function
1 101 AVERAGE()
2 102 COUNT()
3 103 COUNTA()
4 104 MAX()
5 105 MIN()
6 106 PRODUCT()
7 107 STDEV()
8 108 STDEVP()
9 109 SUM()
10 110 VAR()
11 111 VARP()
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jul 9, 2014 at 04:20 AM
Jul 9, 2014 at 04:20 AM
some of your data has unnecessary spaces at the end. all shd be corrected using TRIM function. my computer regional and language reference for entering data is
m/d/yy and not
d/m/yy as perhaps in your computer. so i have changed
the macro is below
there are two input boxes one for date (how it is in columnA)-type carefully the correct date
and second for priority e.g type priority<space>1<no further spsces>
actually in your data in some case prority<spce> <number> and some cases
priority<no space>number.
I have made them uniform
priority<space>number
so the data will be like this in my computer (change date according to your computer) in sheet1
HDNG1 HDNG2 HDNG3
6/30/2014 11:29:00 Priority 4
6/30/2014 7:28:00 Priority 4
6/30/2014 4:05:00 Priority 1
6/28/2014 4:05:00 Priority 1
6/30/2014 4:05:00 Priority 4
6/30/2014 4:05:00 Priority 3
6/28/2014 4:05:00 Priority 3
6/28/2014 4:05:00 Priority 4
try this macro AND SEE SHEET2
IS THIS WHAT YOU WANT????????
SUM of col B which is in TIME format may give peculiar times.
instead of SUM (109) use AVERAGE(101) which is more realistic.
m/d/yy and not
d/m/yy as perhaps in your computer. so i have changed
the macro is below
there are two input boxes one for date (how it is in columnA)-type carefully the correct date
and second for priority e.g type priority<space>1<no further spsces>
actually in your data in some case prority<spce> <number> and some cases
priority<no space>number.
I have made them uniform
priority<space>number
so the data will be like this in my computer (change date according to your computer) in sheet1
HDNG1 HDNG2 HDNG3
6/30/2014 11:29:00 Priority 4
6/30/2014 7:28:00 Priority 4
6/30/2014 4:05:00 Priority 1
6/28/2014 4:05:00 Priority 1
6/30/2014 4:05:00 Priority 4
6/30/2014 4:05:00 Priority 3
6/28/2014 4:05:00 Priority 3
6/28/2014 4:05:00 Priority 4
try this macro AND SEE SHEET2
Sub test()
Dim rdata As Range, rb As Range
Dim ssum As Double, ddate As Date, priority As String, dest As Range
Worksheets("sheet1").Activate
Set rdata = Range("A1").CurrentRegion
Set rb = rdata.Columns("B:B")
'MsgBox rb.Address
ddate = InputBox("type the date to be filtered e.g. 30/6/2014")
priority = InputBox("type the priority priortiy 1-note space")
rdata.AutoFilter Field:=1, Criteria1:=ddate
rdata.AutoFilter Field:=3, Criteria1:=priority
ssum = WorksheetFunction.Subtotal(109, rb)
'MsgBox ssum
With Worksheets("sheet2")
Set dest = .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
dest = ddate
dest.Offset(0, 1) = priority
dest.Offset(0, 2) = ssum
dest.Offset(0, 2).NumberFormat = "hh:mm:ss;@"
End With
ActiveSheet.AutoFilterMode = False
End Sub
IS THIS WHAT YOU WANT????????
SUM of col B which is in TIME format may give peculiar times.
instead of SUM (109) use AVERAGE(101) which is more realistic.
Jul 8, 2014 at 10:57 AM
below is sample for what i need as i need to make function to calculate the summtion or the average of column B when for example i selcte date 30/6/2014 from clumn A and select from C priority one or select priorty 1 and date 28/6/2014
i know the subtotal function but it will make me to make manual filter for each selection i need function to do subtotal for each selection automatic without making manual filter
A B C
30/06/2014 11:29:00 Priority 4
30/06/2014 7:28:00 Priority 4
30/06/2014 4:05:00 Priority1
28/06/2014 4:05:00 Priority 1
30/06/2014 4:05:00 Priority 4
30/06/2014 4:05:00 Priority 3
28/06/2014 4:05:00 Priority3
28/06/2014 4:05:00 Priority 4