1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CountIfs for more than 70,000 rows

Discussion in 'VBA Macros' started by ThrottleWorks, Jan 14, 2019.

  1. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi,
    I am trying below mentioned formula in a worksheet.
    Last row of the worksheet in 70,000 +.
    I tried applying this formula at one go with usual method.


    For example, Range(“A2:A” & TempLr) .FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""YA*"")>0,COUNTIFS(C[1],RC[1],C[9],""SU*"")>0),""Bike"",""Car"")"

    But my machine gets hanged with this operation.
    That is why I am trying below loop. However this loop takes too much time to get complete.
    Calculation mode is turned to manual before loops starts and turn to automatic once loop gets complete.


    Can anyone please help me about how do I make this loop faster.

    Code (vb):

    For Each TRng In TempRng
    MySht.Cells(TRng.Row, 1).FormulaR1C1 = "=IF(AND(COUNTIFS(C[1],RC[1],C[9],""YA*"")>0,COUNTIFS(C[1],RC[1],C[9],""SU*"")>0),""Bike"",""Car"")"
    Next TRng
     
  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    Without sample. Bit hard to help you. But try using array to store your range, and iterate over it in memory. Write back to range in one shot.

    If necessary use Collection/Dictionary to store/perform calculation as needed.
    ThrottleWorks likes this.
  3. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,921
    If you don't need the formula to be available post running the code e.g. you are converting them to values etc then you should use VBA native methods to arrive at the results.
    ThrottleWorks likes this.
  4. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @shrivallabha
    Thanks for the help.
    No, I do not need formula post running the code.
    Could you please help about VBA native functions if you get time.
    Have a nice day ahead. :)
  5. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro
    Thanks a lot for the help.

    Sorry for not uploading sample data.
    Uploading is not allowed.

    One request, could you please help me with Collection / Dictionary for this if you get time.
    I do not know how to use it. Or how should use array for this.
    Tried few solutions from Google but not able to do it.


    I understand that you might not get time to explain.
    Have a nice day ahead. :)
  6. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    At least tell me what columns you are reading/checking for condition(s) and what column it's written back to and what data types are present in each column with few examples.
    ThrottleWorks likes this.
  7. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Yes @Chihiro
    Really sorry for delay.

    Please give me some time for details.
    Data is at different machine (different floor) hence it is taking time for me.
    Thanks a lot for the help. :)
  8. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro please see below details if you get time.

    Original formula is as below

    “=IF(AND(COUNTIFS(B:B,B2,J:J,"Ya*")>0,COUNTIFS(B:B,B2,J:J,"Su*")>0),"Bike","Car")”

    B = Concat, this is a concat of three values, example = “11W1A1EURNot found”
    J has values starting with Ya and Su. We can say starting with Yamaha and Suzuki.
    So if we filter “11W1A1EURNot found” in B column, J column has values starting from both the Ya and Su.


    I have 21 records for “11W1A1EURNot found”, out of these 21, 17 values from column J are starting with Ya. And 4 values are starting with Su hence this is a perfect combination for me.

    So when I applied this formula in column A, I will get result as Bike.
    For any concat value, if we filter on concat value in column B, I should get both the Ya and Su in column J.

    Let us take another example, concat value is “1111AUDNot found”.
    I get two records for this. Both has values starting from Ya in column J.
    There is no value for Su in column J for this concat hence formula for this concat will return Car.


    And I need to work further for Bike results.
    Last edited: Jan 15, 2019 at 2:28 PM
  9. vletm

    vletm Excel Ninja

    Messages:
    4,645
    ThrottleWorks
    Your original formula gives one result from ~70000 rows - Bike or Car.
    Why do You try to do something ~70000 times?
    ThrottleWorks likes this.
  10. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    Hmm, so you are checking for each distinct value in B column. And for subset where B is same value, you check J column for Ya*/Su*.

    Where both is present in subset, you want Bike returned, otherwise Car.

    And you need result returned to Column A for each row?
    ThrottleWorks likes this.
  11. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro sir, yes, you are correct.
    Sorry for making it lengthy for you.
  12. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @vletm sir, thanks for the help.
    I am not able to apply formula at one go.
    My machine gets hanged.

    Hence I am trying loop. Have a nice day ahead. :)
  13. vletm

    vletm Excel Ninja

    Messages:
    4,645
    ThrottleWorks
    Your machine will more possible to hang if You try to do something ~70000times!
    Can You solve separate those number of "Ya*" and "Su*"?
    then You'll have two values ...
    and after that =if(Ya>0; Su>0),"Bike","Car")
    or
    Cannot You send a sample file?
    ThrottleWorks likes this.
  14. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @vletm sir, thanks for the help.
    Strangely machine completes the loop within 40 minutes.
    However while applying formula machine gets hang.

    And I can not wait for 40 minutes to get the loop complete.
    But at present I do not know how to make it faster.

    I am sorry, did not understand
    No, uploading is not allowed. Else I would have definitely uploaded in the first post itself.
  15. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    One clarification needed. Is there only Ya* or Su* in column J. Or other patterns possible?
    ThrottleWorks likes this.
  16. vletm

    vletm Excel Ninja

    Messages:
    4,645
    ThrottleWorks
    1) a sample file:
    make a new excel-file and
    make there 20 row data which has data as Your 'huge'-file
    or
    You can write a sample data straight here between correct tags
    remember to name which column is which

    2) do TWO formulas 'somewhere' ..
    = COUNTIFS(B:B,B2,J:J,"Ya*")
    = COUNTIFS(B:B,B2,J:J,"Su*")

    do third formula also 'somewhere'
    = if(Ya>0; Su>0),"Bike","Car")
    >>
    then You will get as You've tried to do.
    ThrottleWorks likes this.
  17. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro sir, thanks for the help.
    Below combinations are possible in data.
    Other than this there are no different values.

    Combination

    Ya Ac

    Ya Dc

    Su Ac

    Su Dc

    Please note, I have used same number of characters and spacing in dummy value.

    So pattern of original data will be same as above.
  18. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
    Try below. On my test workbook using 70k rows. Took less than 1 sec.
    Code (vb):
    Sub Demo()
    Dim ar, res
    Dim i As Long
    Dim sTime As Single, eTime As Single
    sTime = Timer
    With Sheets("Sheet1")
        ar = .Range("B2:J" & .Cells(Rows.Count, "B").End(xlUp).Row).Value
    End With
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(ar)
            If .Exists(ar(i, 1)) Then
                .Item(ar(i, 1)) = IIf(InStr(.Item(ar(i, 1)), Left(ar(i, 9), 2)), .Item(ar(i, 1)), .Item(ar(i, 1)) & Left(ar(i, 9), 2))
            Else
                .Item(ar(i, 1)) = Left(ar(i, 9), 2)
            End If
        Next
    ReDim res(1 To UBound(ar), 1 To 1)
        For i = 1 To UBound(ar)
            If Len(.Item(ar(i, 1))) >= 4 Then
                res(i, 1) = "Bike"
            Else
                res(i, 1) = "Car"
            End If
        Next
    End With
    Sheets("Sheet1").Range("A2").Resize(UBound(res)) = res
    eTime = Timer
    Debug.Print eTime - sTime
    End Sub
  19. vletm

    vletm Excel Ninja

    Messages:
    4,645
    ThrottleWorks
    a) seems that You need few more countifs-formulas...
    b) did I understood correct that there are 5-7 column *~70000 rows formulas...
    and after that You wonder ... why slow/hang?
    ThrottleWorks likes this.
  20. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro sir, thanks a lot for the help.
    Please give me some time to check. I will revert shortly. :)
  21. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro sir, you are :awesome:

    It is working great.
    Please give me some time to understand your code. There is no Ya and Su.
    No formula. You are just awesome.

    Thanks a lot sir. Have a nice day ahead. :)

    Sir, please correct me if I am wrong, it seems ("Scripting.Dictionary") is really powerful.

    I remember @shrivallabha helping me with a vLookUp using ("Scripting.Dictionary"). How do I read more about Scripting Dictionary.

    What are the ways where I can used Scripting Dictionary.
    Any leads will be helpful. :)
  22. Chihiro

    Chihiro Excel Ninja

    Messages:
    5,171
  23. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @vletm sir, sorry for late reply.

    You are correct. Thanks for the help. Have a nice day ahead. :)
  24. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,923
    Hi @Chihiro sir, thanks a lot for the help.

    Analystcave is really good site. Have a nice day ahead. :)

Share This Page