Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a 2 worksheets in an Excel document. I one worksheet I want to compute whether a value is in a column of the second worksheet. The second worksheet's column is a list of phone numbers on the do no call list. The goal is to find which phone numbers in the first sheet are in the do not call list.

My formula uses the MATCH() function:

=MATCH(A2, 'DNC Worksheet'!$C$2:$C$100000, 0)

The problem I am having is that the column of data the formula is looking at is very big (~100,000 rows). I am computing this formula around 25,000 times. The result is that Excel is very slow to open, save, and scroll the file.

Is there a more performant way of doing this kind of search? Perhaps there is a way I can cache the do not call numbers in a data structure?

share|improve this question
    
If you have MS Access, then you might have better performance by importing the data into Access and using that to do your calculations. –  Kibbee Feb 5 '13 at 20:36

1 Answer 1

up vote 7 down vote accepted

There is a way to significantly speed this up (factor 3,000, see explanation below): If you sort the data in column C in worksheet DNC Worksheet, you can run the MATCH without the 0 at the end, i.e. =MATCH(A2,'DNC Worksheet'!$C:$C). (Side note: Excel 2007 and higher is pretty smart when it comes to using the full column, so no need to specify the 100000 here!).

The initial downside of this approach is that you'll also get a match, even if A2 is not included in column C. However, this can be dealt with using the following formula:

=IF(INDEX('DNC Worksheet'!$C:$C,MATCH(A2,'DNC Worksheet'!$C:$C))=A2,MATCH(A2,'DNC Worksheet'!$C:$C),"no match")

Of course, you could half the calculation time of this, by having the match in one cell (say B2)- and then use this in the next cell =IF(INDEX('DNC Worksheet'!$C:$C,B2)=B2,B2,"no match").

Some background:

If you provide MATCH (or VLOOKUP) with the 0/FALSE parameter at the end, Excel performs an exact search, i.e. it starts with the first cell in C2, checks if it matches. If not, it continues to the next until it either finds the value - or produces an N/A! error. Therefore, if you have 100,000 dataset, it will take on average 50,000 comparison until the value is found (=n/2) - so in your case the total number of calculations is 1.25B!

If the FALSE parameter is omitted, Excel assumes that the range is sorted and applies a binary search algorithm: It starts in the middle of the range, i.e. cell 50,000 and checks if its value is larger or smaller. Assuming it's larger the value you're trying to match must therefore be between cell 1 and 50,000. Now it again checks in the middle of this range, etc. Therefore, it will take log2(n) comparison, in your case ~17. So the total number of calculations is a mere 425K, i.e. it'll calculate 3,000x faster! :-)

For further reading/performance tweaks, I recommend this website!

share|improve this answer
1  
+1 for insightful explanation & solution. –  mtone Feb 5 '13 at 21:06
1  
@Peter Albert Your formula does not have balanced parentheses. Does Excel not require that? –  hekevintran Feb 5 '13 at 21:18
    
@hekevintran: good catch, I edited the formula. –  Peter Albert Feb 5 '13 at 21:20
    
Great answer! Everything is smooth again. –  hekevintran Feb 5 '13 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.