vlookup large data set

[Stuart]

Hi,

I am having difficulty dealing with a very large data set that I have
in excel. I have a list of around 200,000 part numbers which must be
looked up to see if they are contained in the master part number list,
which is 60,000.

Excel is grinding to a halt as for every part number its attempting to
go through 60,000 lines and find it. Worst of all excel is having to
do this 200,000 times.

Any suggestions on how to deal with this? I have tried splitting the
data up into sheets and running the vlookup on each sheet but that
didnt really make much difference.

Not sure if this is possible anymore :/

 

[Jason]

Hi Stuart,

Excel should be able to handle 200,000 vlookups, although it will depend
on your cumputer.

First of all, are you using the FALSE argument in your vlookups? If so,
this will greatly add to the calculation time. If possible sort your
60,000 row master list by the part number field, in ascending order.
Then replace the FALSE in all your vlookups with TRUE (or nothing,
because if neither TRUE/FALSE is explicitly stated, TRUE will be
assumed). This will speed it up significantly.

Also,are you error trapping your N/As with a double lookup? along the
lines of:

IF(ISNA(VLOOKUP(A1,range,2,FALSE)),"",VLOOKUP(A1,range,2,FALSE))

If so *don't* as you're effectively doubling the lookups. Look for an
alternative means of handling N/As, such as conditional formatting.

There are many other ways of increasing Lookup efficiency (many
involving not using VLOOKUP but other functions), but let me know how
you get on before I suggest any more.

Jay

 

[Don Guillett]

Try to break up into smaller blocks to look at >name them .
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.

 

[Stuart]

Hi Jay

Yes I am using the false argument - when I use true and the master
data list is sorted I get weird results. Basically i need it to be an
exact match and not a partial match.

Hi Don,

Im not 100% sure what you mean by look at >name ?

 

[Don Guillett]

Name the smaller blocks with defined names to make self adjustable.

 

[Dave Peterson]

If you're filling all 200,000 rows with formulas, you could try:

Fill the top cell with the formula.
Drag it down a 1000 rows or so.
Let it recalc
Change the first 999 cells to values
Drag down that last formula another 1000 rows or so.
Repeat as necessary.

If you have to do this lots of times, you may want to experiment to see how many
rows can be filled with the formula before excel gets really tired...

And then use that in a macro that would do what you've done manually.

 

[Jason]

I'm not sure what you mean by 'weird' results. Using the TRUE argument
will still return *exact* matches when the master data list is sorted in
ascending order. Have you got any examples?

I'd try changing to TRUE before trying calculating the lookups in blocks
of rows

Jay

 

[Stuart]

Some of the part numbers have letters in them, i think thats why it's
returning partial matches

 

[Don Guillett]

I repeat my offer to look