+ Reply to Thread
Results 1 to 22 of 22

Find Count of filter, Advanced filter Data

  1. #1
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Find Count of filter, Advanced filter Data

    Hi Guys,

    I need an assistance to plot a table using VBA. I have a huge data on which I need to apply filter, an advanced filter and find a count of the records. I am attaching a sample data with rules to filter data.

    can anyone help me in this?
    Attached Files Attached Files

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    This will work

    Please Login or Register  to view this content.
    Last edited by mike7952; 01-05-2015 at 04:58 AM.
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Find Count of filter, Advanced filter Data

    Done it.
    Instructions:-
    Goto to Developer Tab
    Click macros
    Run AutoFilter_Count Macro
    You can change the Criteria Arrays where commented.

    Please Login or Register  to view this content.
    Check the attached file:-
    Attached Files Attached Files
    Regards,
    Vikas Gautam
    Excel-buzz.blogspot.com

    Excel is not a matter of Experience, its a matter of Application.

    Say Thanks, Click * Add Reputation

  4. #4
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Find Count of filter, Advanced filter Data

    Please find the attached sheet and click on the Orange Button to get the desired output.

    I have shifted your data down to row 4. And if you add more months in count table and more data in the data table, the code will count all.
    Attached Files Attached Files
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  5. #5
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Hi Mike, This code creates table with header and no values in it.

    Also I wanted to understand logic behind the code. I am looking for 'CI' in specific column. I think above code will search for "SWR", "HWR", "LHI" in entire selected range. please correct me.

  6. #6
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Hi Vikas & sktneer,

    I get error when I change your code to suite my requirement: "autofilter method of range class failed"

    I am just changing Field column as per my requirement. in my worksheet AssignedGroup column is at 18th position. so I changed it as

    Please Login or Register  to view this content.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by linok View Post
    Hi Vikas & sktneer,
    I get error when I change your code to suite my requirement: "autofilter method of range class failed"
    That's why you should have uploaded a sample workbook with the layout as same as of your original workbook specially when you are asking help for a VBA solution and if you don't do so that simply means that you are capable enough to understand the codes provided and able to change them to suit your original workbook's layout and requirement.

  8. #8
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by sktneer View Post
    That's why you should have uploaded a sample workbook with the layout as same as of your original workbook specially when you are asking help for a VBA solution and if you don't do so that simply means that you are capable enough to understand the codes provided and able to change them to suit your original workbook's layout and requirement.
    I strongly agree to you. I had to edit sample workbook so that generous peoples like you don't get confused with other junk data. Also my workbook has around 20 columns and I wanted to make sure that I am not publishing any sensitive data. I hope you understand this.

    About Autofilter do you have any clue about 'Field' , I am not sure why it is not working. Please find attached file.
    Attached Files Attached Files
    Last edited by linok; 01-06-2015 at 02:17 AM.

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    @linok,

    I'm not able to get to a computer but in your example workbook in your first post , does my code that I gave you work correctly for you?

    Also going off memory I believe your requirement was to look for those 3 values SWR, HWR and LHI nothing about CI. I don't believe 3 of us got that part wrong.
    Last edited by mike7952; 01-06-2015 at 02:19 AM.

  10. #10
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by mike7952 View Post
    @linok,

    I'm not able to get to a computer but in your example workbook in your first post , does my code that I gave you work correctly for you?

    Also going off memory I believe your requirement was to look for those 3 values SWR, HWR and LHI nothing about CI. I don't believe 3 of us got that part wrong.
    Yes , It does. Probably I need to see it bit closer & change it as there are no comments in the code
    Last edited by linok; 01-06-2015 at 02:26 AM.

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    So then we are lookin for SWR, HWR and LHI? And Not CI

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    Code is looking for those three variables in column C. Where a(i,3) is for column C
    Please Login or Register  to view this content.

  13. #13
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Find Count of filter, Advanced filter Data

    In Field:= ?, you need to replace ? with column index of the criteria column in range on which an autofilter is being applied.
    So if you are applying Autofilter on say range("A:G") and your criteria column is col. D, you will use Field:=4 as col. D is the fourth column in the range taken.
    Now suppose your range for autofilter is range("D:G") and your criteria column is again col. D, you will use Field:=1 as in this case col. D is the first column in the range taken.

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    I might be able to help if you can tell what row your data starts on and what columns is your data in? Month column, database, sever, applications column and your other column that has the SWR LHI

  15. #15
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by sktneer View Post
    In Field:= ?, you need to replace ? with column index of the criteria column in range on which an autofilter is being applied.
    So if you are applying Autofilter on say range("A:G") and your criteria column is col. D, you will use Field:=4 as col. D is the fourth column in the range taken.
    Now suppose your range for autofilter is range("D:G") and your criteria column is again col. D, you will use Field:=1 as in this case col. D is the first column in the range taken.
    My Range is A:S and my filter column is R which is 18th column. I have changed this in your code. but it does not work for me. I know it could be silly mistake. looking in to it.

  16. #16
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by mike7952 View Post
    I might be able to help if you can tell what row your data starts on and what columns is your data in? Month column, database, sever, applications column and your other column that has the SWR LHI
    I have provided another sample file which is more realistic. I could figure out your code for 2 columns i.e "Applications, server, database" & "SWR", "HWR", "LHI". still looking for column "Month"
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    12-02-2014
    Location
    India
    MS-Off Ver
    2010
    Posts
    74

    Re: Find Count of filter, Advanced filter Data

    Quote Originally Posted by linok View Post
    I have provided another sample file which is more realistic. I could figure out your code for 2 columns i.e "Applications, server, database" & "SWR", "HWR", "LHI". still looking for column "Month"
    Found line of code for remaining column as well
    Mike, Your code works for me. Sorry for trouble.
    Last edited by linok; 01-06-2015 at 02:58 AM.

  18. #18
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Find Count of filter, Advanced filter Data

    Month is column A witch is 1.

  19. #19
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Find Count of filter, Advanced filter Data

    Changed the code as per your workbook, see if this works.
    Attached Files Attached Files

  20. #20
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Find Count of filter, Advanced filter Data

    This should work
    Please Login or Register  to view this content.
    Attached Files Attached Files

  21. #21
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Cool Re: Find Count of filter, Advanced filter Data

    I have sorted this out.
    Actually the problem was because of the word "Group" behind each criteria in Field 18.
    Its working now. The result is on second sheet.
    Here is the revised code:-
    Please Login or Register  to view this content.
    Check the attached file:-
    Attached Files Attached Files
    Last edited by Vikas_Gautam; 01-06-2015 at 03:27 AM.

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Find Count of filter, Advanced filter Data

    linok, Thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Advanced Filter : One Criteria Cell Breaks the Filter - Returns Only Headers
    By PaulGW in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-28-2014, 10:06 AM
  2. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  3. Second Advanced Filter does not work based on the data generated from the filter
    By mucc1928 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-29-2011, 02:42 PM
  4. Cannot find Unmatched Data via Advanced Filter
    By rgalang in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-12-2011, 05:20 AM
  5. [SOLVED] advanced filter a range:Advanced Filter function
    By Il Principe in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 10:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1