CountIfs for more than 70,000 rows

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.

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``````

Chihiro

Excel Ninja
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.

shrivallabha

Excel Ninja
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

Excel Ninja
Hi @shrivallabha
Thanks for the help.
No, I do not need formula post running the code.

ThrottleWorks

Excel Ninja
Hi @Chihiro
Thanks a lot for the help.

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.

Chihiro

Excel Ninja
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

Excel Ninja
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.

ThrottleWorks

Excel Ninja
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.

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:

vletm

Excel Ninja
ThrottleWorks
Your original formula gives one result from ~70000 rows - Bike or Car.
Why do You try to do something ~70000 times?

Chihiro

Excel Ninja
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

Excel Ninja
Hi @Chihiro sir, yes, you are correct.
Sorry for making it lengthy for you.

ThrottleWorks

Excel Ninja
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.

vletm

Excel Ninja
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

Excel Ninja
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*"?

Chihiro

Excel Ninja
One clarification needed. Is there only Ya* or Su* in column J. Or other patterns possible?

vletm

Excel Ninja
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

Excel Ninja
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.

Chihiro

Excel Ninja
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``````

vletm

Excel Ninja
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

Excel Ninja
Hi @Chihiro sir, thanks a lot for the help.
Please give me some time to check. I will revert shortly.

ThrottleWorks

Excel Ninja
Hi @Chihiro sir, you are

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.

ThrottleWorks

Excel Ninja
Hi @vletm sir, sorry for late reply.

did I understood correct that there are 5-7 column *~70000 rows form
You are correct. Thanks for the help. Have a nice day ahead.

ThrottleWorks

Excel Ninja
Hi @Chihiro sir, thanks a lot for the help.