Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |