+ Reply to Thread
Results 1 to 5 of 5

faster way to do countif via vba for large dataset

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    faster way to do countif via vba for large dataset

    Hi,

    I am enclosing a test file which is just a small sample of the dataset I actually have to consider.

    Since the data set I am working with is large I cant use the application.countif type function.........its just way tooooo slow.


    using the enclosed file: what I am trying to calculate is the following:

    if I were to use equations in excel they might look like the following:

    =countif(
    ColN, "Open",
    ColF, "Site*",
    ColG, H2,
    ColX, <= I2,
    ColO, > DefnNameTAT)

    +

    countif(
    ColN, "Open",
    ColF, "Site*",
    ColG, H2,
    ColX, =&"All",
    ColO, > DefnNameTAT)


    Note: DefnNameTAT is a user defnd name and its value is located in M1


    I started the macro..........i thought using a scripting dictionary would be helpful.....


    I have probably confused someone......if so let me know.


    note: i had to keep deleting rows from my input file so that it would make the 1000kB file size requirment.........but i did not go back and update the last row values i put into the macro.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: faster way to do countif via vba for large dataset

    You could try using autofilter to get your conditions and then subtotal to count non-hidden rows.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: faster way to do countif via vba for large dataset

    can you provide an example?

    Lately when i have "large files" i tended to stay away from anything excel-function like because i keep getting burned by things like "oh yeah that does not work when you have that many elements"........so i have tended to keep using arrays and i know scripting dictinary is a good tool.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: faster way to do countif via vba for large dataset

    turns out i asked this question already........so sorry.

    HTML Code: 
    http://www.excelforum.com/excel-programming-vba-macros/952748-more-efficient-way-to-do-for-loop-using-application-worksheetfunction-countifs.html
    i will review this post by nilem and then i will post soln for the example i asked about.

  5. #5
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2007
    Posts
    2,535

    Re: faster way to do countif via vba for large dataset

    ok.....i was able to do it with the help of the previous forum post.

    i am enclosing the file. note: to verify that the vba code worked i calculated the results manually using the excel formulas.

    manual calculation is shown in column D on the worksheet "SmallerData_set". Col-E contains the results from the vba program.

    Again, thanks to the previous post........

    note: to do the manual calc i had to save file format into the xl2007 format.

    if there is a better way than what i did please let me know
    Attached Files Attached Files

+ 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. Replies: 3
    Last Post: 01-23-2014, 10:13 AM
  2. How to calculate average in a large dataset
    By dolle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-27-2013, 01:08 PM
  3. [SOLVED] Finding ALL CAPS in large dataset
    By renee705 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2012, 02:35 PM
  4. [SOLVED] Graphing a large dataset
    By CTM2012 in forum Excel General
    Replies: 6
    Last Post: 06-18-2012, 03:48 PM
  5. Graphing a large dataset
    By CTM2012 in forum Excel General
    Replies: 9
    Last Post: 06-18-2012, 10:20 AM

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