CODEWITHSUNDEEP
excelvbaruntime
Presen
Presen

Reputation: 1867

Excel VBA large data runtime issue

I have large scale data (700K rows), and I'm trying to count the number of appearance of a word within the rows, and do so for also many times (50K iterations). I'm wondering if Excel is appropriate platform, using VBA or maybe COUNTIFS, or should I use different Platform? If so, is there a platform that has similarity points to Excel and VBA? Thanks!

Upvotes: 0

Views: 164

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33175

With your small sentences in column A and the 700k lines in column A of Sheet1, this formula will count the occurrences. It's an array formula and must be entered with Ctrl+Shift+Enter.

=SUM(--NOT(ISERR(FIND(A2,Sheet1!$A$1:$A$700000))))

To calculate 200 small sentences took about 20 seconds on my machine. If that's an indication, it will take about 1.5 hours to calculate 50k small sentences. You should probably find a better tool or at least hit calculate right before you leave for lunch. Definitely test it on a smaller number to make sure it gives you the answers you want. If you don't have to do this often, maybe 1.5 hours is palatable.

Upvotes: 1

Related Questions