Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with large file of zipcodes
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with large file of zipcodes
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with large file of zipcodes
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 "DTTODGG" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup with large file of zipcodes
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 on your table are ending the form with TRUE or FALSE "Charles Williams" wrote: 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 "DTTODGG" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you save 1 sheet from a large file into another file? | Excel Worksheet Functions | |||
Purging ZIPCODES from a string of cities and zips | Excel Worksheet Functions | |||
Not printing 0 in zipcodes from excel spreadsheet. | Excel Discussion (Misc queries) | |||
How do I convert 9 digit zipcodes to 5 digits? | Excel Discussion (Misc queries) | |||
creating spreadsheet with colunms for names adresses & zipcodes | Excel Discussion (Misc queries) |