COUNTIF crashes Excel

1
votes

I have an Excel sheet with date and email address columns, sorted by date. I want to count the number of times an email address is in the sheet prior to the current occurrence. The COUNTIF(B$1:B1,B2) formula works but when I copy it down to more than 50,000 records Excel crashes. I have 200,000 records total.

Is there another solution that Excel (2010) can handle?

2
excelexcel-2010
I think people use VBA for large data sets as described here: stackoverflow.com/questions/6134002/… – dcaswell
Have you tried disabling automatic calculation, and then manually calculating (I think F9)? – David Zemens
Is it actually crashing, or does it go into (not responding)? – Pynner
crashed, on both mac and PC – uwe

2 Answers

1
votes

Here's a VBA sub that runs in a reasonable time

Sub countPrior()
    Dim dic As Object
    Dim i As Long
    Dim dat As Variant
    Dim dat2 As Variant

    ' Get source data range, copy to variant array
    dat = Cells(1, 2).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1)
    ' create array to hold results
    ReDim dat2(1 To UBound(dat, 1), 1 To 1)
    ' use Dictionary to hold count values
    Set dic = CreateObject("scripting.dictionary")
    ' loop variant array
    For i = 1 To UBound(dat, 1)
        If dic.Exists(dat(i, 1)) Then
            ' return count
            dat2(i, 1) = dic.Item(dat(i, 1))
            ' if value already in array, increment count
            dic.Item(dat(i, 1)) = dic.Item(dat(i, 1)) + 1
        Else
            ' return count
            dat2(i, 1) = 0
            ' if value not already in array, initialise count
            dic.Add dat(i, 1), 1
        End If
    Next
    ' write result to sheet
    Cells(1, 3).Resize(Cells(Rows.Count, 1).End(xlUp).Row, 1) = dat2
End Sub
0
votes

If macros are an option for you, here is a macro that will accomplish this for you. I have assumed your address is in column B and you wish to record the number of prior occurrences in column C, you can modify it for the way your sheet is structured.

Sub countPrior()
Application.ScreenUpdating=False
bottomRow = Range("B1000000").End(xlUp).Row
   For i = 2 To bottomRow
   cellVal = Range("B" & i).Value
   counter = 0
      For j = 1 To i - 1
         If Range("B" & j).Value = cellVal Then
         counter = counter + 1
         End If
      Next j
   Range("C" & i).Value = counter
   Next i
Application.ScreenUpdating=True
End Sub