Working with large data files

[algioia]

I work with large excel files, some are around 150MB. If I add formulas to the file, just basic vlookups, the processing takes forever and usually freezes up excel, it will sometimes stop responding and I lose my changes. Other programs are not affected. I explained this to our IT person and they purchased additional RAM for my laptop, I now have 32GB of RAM. However, I don't believe it made any type of difference. Would it be the processor that I would need to improve? I guess I'm asking what type of machine / features do I need to make excel process formulas and data at a faster speed? I basically just use Adobe Standard, Excel and Outlook for 90% of my work. Any input is appreciated.

Also, inserting subtotals isn't ideal either. I've attempted it and let it start to run and it would be under 10% complete after 5 minutes or so.

I have 64-bit Office. CPU / Memory in task manager stay below 40% while excel is processing the data.


Thank you!

[MrShorty]

Not much to go on. You say that you are using basic VLOOKUP()s without any detail. If these basic lookups are linear "exact match" lookups (4th argument of VLOOKUP() is FALSE or 0), then you need to know that basic exact match lookups are very slow on large data sets. When the topic comes up, I see dramatic improvements by doing what is needed to switch to much more efficient binary "approximate match" lookups (4th argument of VLOOKUP() is TRUE or 1 or omitted). This means sorting the main database, but there are potentially significant performance improvements to be made.

[algioia]

Ok, then not much I can do. I use FALSE in the vlookup string. For example, I am looking up this value: Katy, TX 77449. The lookup table is sorted. I am looking up sales tax rates based off of city, state and zip code so it really has to be an exact match. Thanks for the reply.

[MrShorty]

it really has to be an exact match.

I can't remember where I read it, but somewhere I read that over a database as small as like 50 or 100 rows, it is faster for the computer to do two binary searches than it is to do one linear search. In other words, this:
=IF(VLOOKUP(lookup_value,lookup_table,1,TRUE)=lookup_value,VLOOKUP(lookup_value,lookup_table,5,TRUE),NA())
on a large data base is faster than:
=VLOOKUP(lookup_value,lookuptable,5,FALSE)

Even if exact match is a necessary part of the algorithm, I would expect that there are significant performance gains to be realized by exploring better search algorithms than VLOOKUP's linear exact match algorithm.

[alansidman]

Without understanding or seeing your workbooks, it may be hard to provide you with an alternative solution. Maybe a sample workbook of a before and after scenario? Only need a half dozen or so records to demonstrate what you are trying to do.