Vlookup
 

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?

Vlookup
 

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.
DataFilterAutofilter 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


nc wrote:
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?

Vlookup
 

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



"nc" wrote:

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?

Vlookup
 

Thanks Roger.

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



"Roger Govier" wrote:

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.
DataFilterAutofilter 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


nc wrote:
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?