• 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...

  • 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

ThrottleWorks

ThrottleWorks

Excel Ninja
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: 
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
 
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.
 
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.
 
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. :)
 
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.
 
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. :)
 
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:
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?
 
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. :)
 
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?
 
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
Can You solve separate those number of "Ya*" and "Su*"?
Click to expand...

No, uploading is not allowed. Else I would have definitely uploaded in the first post itself.
 
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.
 
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.
 
Try below. On my test workbook using 70k rows. Took less than 1 sec.
Code: 
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
 
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. :)
 
You must log in or register to reply here.
Back
Top