VLookUp does not require that the sheets be sorted. At least, the version
of VLookUp you want to use doesn't. I wish people would stop suggesting
this. :-(
It is only when you are doing range VLookUps that you need to sort the
lookup sheet.
When you are doing exact matches (false as the last parameter), the sheet
does not need to be sorted.
If all you want to do is to return the current price, then you probably
don't need a program at all. Just put VLookUps down the column to grab the
new prices, then copy them where you want them.
However, 40,000 searches through 60,000 items is going to take significant
time, no matter how you do it in Excel. Usually, data of that magnitude
would be more suitably handled in a database.
Regards, Dave S
----- Original Message -----
From: "mickey11030" <mickey11030@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Tuesday, August 30, 2011 5:20 AM
Subject: [ExcelVBA] Relative Speed: Find vs VLookup
>I have a worksheet with about 40,000 products, and a second worksheet with
>about 60,000 products. I have to match each of the 40,000 product IDs in
>the first sheet with the corresponding product IDs in the second to
>retrieve the current price. Not all the 40,000 products have matching IDs.
>
> I have written a VBA program based on the Find method that does this
> processing, but it runs for a couple of hours. Someone suggested the
> program would run faster if I used VLookup instead. I understand that
> using VLookup requires that the second sheet be sorted. I did a sort and
> was astonished that it was done almost instantaneously, so that would not
> be a concern.
>
> Before I do all the work involved in writing a new program based on
> VLookup, I wonder if anyone knows if that program is likely to run faster.
>
> Thanks,
> Michael Trombetta
>
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 10.0.1392 / Virus Database: 1520/3865 - Release Date: 08/29/11
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar