VLOOKUP hang-time...
 

I seem to be having an issue with VLOOKUP and mucho data.

My Excel spreadsheet opens and asks for user input as to the location
of a .CSV file that is in the vacinity of 6500+ records. It adds this
as a new sheet and uses the VLOOKUP function to process the records
into human readable form: (From the macro that copies the formula down
the column)

ActiveCell.FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],ZipCodes!R2C2:R33108C3,2,FALSE))," & """NOT
FOUND""" & ",VLOOKUP(RC[-1],ZipCodes!R2C2:R33108C3,2,FALSE))"

The VLOOKUP has to reference a zipcode sheet that contains
approximately 32,000 zipcodes/state references.

CPU usage pegs out at 100% and Excel freezes. Am I asking too much
from it? Should I move to Access?

Thanks for any pointers...

JC

VLOOKUP hang-time...
 

Hi
so you have 6500 VLOOKUP formulas. If yes this could probably be a
little bit too much.
Try first: Disable automatic calculation and only manually
re-calculate.
Also paste the results afterwards with 'Edit - Paste Special - Values'
as values to remove the formulas

--
Regards
Frank Kabel
Frankfurt, Germany

"John Croson" schrieb im Newsbeitrag
om...
I seem to be having an issue with VLOOKUP and mucho data.

My Excel spreadsheet opens and asks for user input as to the location
of a .CSV file that is in the vacinity of 6500+ records. It adds this
as a new sheet and uses the VLOOKUP function to process the records
into human readable form: (From the macro that copies the formula
down
the column)

ActiveCell.FormulaR1C1 =
"=IF(ISNA(VLOOKUP(RC[-1],ZipCodes!R2C2:R33108C3,2,FALSE))," & """NOT
FOUND""" & ",VLOOKUP(RC[-1],ZipCodes!R2C2:R33108C3,2,FALSE))"

The VLOOKUP has to reference a zipcode sheet that contains
approximately 32,000 zipcodes/state references.

CPU usage pegs out at 100% and Excel freezes. Am I asking too much
from it? Should I move to Access?

Thanks for any pointers...

JC