Vlookup with large file of zipcodes

[DTTODGG]

Hello,

I have a reference file with 3 columns that has 50702 rows:
Zipcode (text) DMARank (Numeric) DMACode (Numeric)
01105 109 543
99950 207 747

I have other files with address data (including zipcodes):
Name Address City State Zip DMACode
Bob 123 North Springfield MA 01105 109
I would like to add the DMARank to the last column in this file, but I get
an error:
"Excel cannot complete this task with available resources. Choose less data
or close other applications."
I don't believe the error is correct, I can close almost everything and it
still gives me the error. I can run it on another computer and still get the
error.

Could it be the zipcodes are "text" because some of them begin with "01105"
and cannot therefore be numeric? Should I convert to numeric, drop the
leading zero in both files, then add it back later?

Is this too much for excel? Should the DMA file be a database or something?
Do I need to divide the file into smaller pieces? Would Match/Index work
better?

Please help me find a workable solution, we use these DMACodes with many
files and I need to find a way to make it work.

Thank you so much!

 

[Don Guillett]

Excel has a hard time getting data from a LARGE EXTERNAL file. Try putting
the data in the same file.

 

[google]

BTW, Excel has provisions for formatting Zip and Zip+4 cell contents.
Look into Format->Cell->Numbering->Special. I would stay with these as
opposed to forcing text or number formats.

 

[Charles Williams]

You may have too many external links if the reference file is closed: make
sure your reference file is already open when you are working with your
other files.

and see http://www.decisionmodels.com/optspeede.htm for advice on using
Lookups efficiently.

regards
Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

 

[Dylan @ UAFC]

I think you may want to think of what the absoult unique
data entry of your table area, a unque column with no duplicated
data to pull from.
I am working a 5000000 row data base that is working error free
The key to having great results in table array's great orgaized and unique
data