Vlookup

N

nc

Hi

I have two tables, one has 23,000 rows and the other 21,000. I am using
vlookup to using the customer id to check which id does not exit in the
latter table. Unfortunately this takes a while to recalculate. Is there any
solution to speed up this process?
 
R

Roger Govier

Hi

Assuming the sheet with the larger number of names is Sheet1, and the names
are in column A, then
=COUNTIF(Sheet2!$A$1:$A$21000,"="&A1)
copy down the column.
It will return a 1 where the name is present and 0 where not present.
Data>Filter>Autofilter select the dropdown on the column with your formulae
and Select 0 to show the list of names not present on sheet2.

You can copy the filtered data and paste to the other sheet if required.

Regards

Roger Govier
 
C

CLR

You might consider using VBA to insert and copy down the VLOOKUP's, and then
it can also do "copy > paste-special > values" on that column......from then
on, working with the sheet will go faster.......later you can run the macro
again if needed.

Vaya con Dios,
Chuck, CABgx3
 
N

nc

Thanks Roger.

Your solution did help. I am frustrated because I have a lot functions in
the cells the recalculate is very slow.
 
Top