Count if Macro

[zraza]

I want to create a 'Count If' macro to check data in 800K rows. Here is my sample data:

A B C D
Date ID-1 ID-2 Result
1-Jan-17 123456 123456 1
1-Jan-17 234567 345678 0
1-Jan-17 345678 1

I want to look results of column-B (ID-1) in column-C (ID-2). Column-C is my range and Column-B is my criteria. I want results populated in column-D.

Fyi, there are no duplicates in any column. Basically, I want to do a simple True/False (1/0) check and I am using COUNTIF function in excel. However, due to processing limitations for large data set I can't use it.

 

[L. Howard]

What would be the IF statement in D for each one of the three examples you show?
Just type it out in plain speak, something like... if b equal c then 1

I don't understand why the first and the third both return a 1?

Howard

 

[zraza]

Hi,

Here is my formula in cell D.

=COUNTIF($C$2:$C$800000,$B2)

This formula looks B2 cell value in column C range and populates results in column D as 1 or 0. If it founds value it puts 1 or else vice-versa.

The first one (123456) returned '1' because it was in the range, second one (234567) returned '0' as it was not in range and third one (345678) returned '1' because it was also in range.

 

[L. Howard]

I have had dismal luck using this normally reliable macro to autofill a vast column with formulas. With different formulas, I have used it for many thousands of rows, and the timer returns a few seconds at most on nearly all my prior uses.

I suspect the countif and the large number of rows is a calculating nightmare. Tried turning calc to manual, but did not seem to help. I had only one successful run of about 26 seconds with 800K rows. Others locked up my laptop and I had to bail out.

The : .Value = .Value returns the formula value, leaving NO formulas in the filled cells. You can do away with it using just this line instead...

.Formula = "=COUNTIF($C$2:$C$800000,$B2)"

You may give it a try, adjusting whatever columns and rows or ranges to suit your sheet.

Sorry I cannot offer a more promising solution.

Howard

Sub Tester_Fill()

  Dim lRowCount&, dTimer#
  dTimer = Timer: lRowCount = Cells(Rows.Count, "B").End(xlUp).Row
  
  With Range("D1").Resize(lRowCount)
    .Formula = "[B]=COUNTIF($C$2:$C$800000,$B2)[/B]": .Value = .Value
  End With
  

 MsgBox Format(Timer - dTimer, "0.000\,000\,000")
  
End Sub

 

[Peter_SSs]

1. Do columns B and C both have 800,000 rows? If not can you tell us approximately how many rows there might be in each?

2. Can you confirm or correct my understanding? You said no duplicates in either column B or C, so all the results in column D will be 1 or 0?

3. If the above is correct, would you be just as happy with results in column D of 1 or blank? (it would shave a small amount of time off what I have in mind so far)

 

[zraza]

1. Approximately 800K to 850K rows in both columns B and C.
2. Correct. The output will be 1 or 0 only.
3. I don't know how this will work as my excel formula doesn't specify 1 or 0. It retruns 1 if value is there otherwise it returns 0.

 

[Peter_SSs]

1. Approximately 800K to 850K rows in both columns B and C.
2. Correct. The output will be 1 or 0 only.
3. I don't know how this will work as my excel formula doesn't specify 1 or 0. It retruns 1 if value is there otherwise it returns 0.

Thanks.
I have relied on the fact that each column contains no duplicates.

My test data consisted of around 730,000 unique 6-digit numbers in each column. Approximately 590,000 numbers were common to both columns.
On my old desktop the code took about 90 seconds. On a much newer laptop, with identical data, about 55 seconds.

I'd suggest testing on a smaller data set initially, and certainly on a copy of your workbook if you think the smaller test is doing what you want.

Sub Count_If()
  Dim d As Object
  Dim b As Variant, c As Variant, itm As Variant
  Dim i As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  c = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value
  For Each itm In c
    d(itm) = 1
  Next itm
  b = Range("B2", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(b)
    b(i, 1) = Val(d(b(i, 1)))
  Next i
  Range("D2").Resize(UBound(b)).Value = b
End Sub