Need some pointers in the right direction [Large Excel files]

Hello guys,

I'm the new guy in my department in a big company and from what I see the work here could be much improved because people are losing so much time with large excel files.
At the moment they are using Excel for preparing the CSV files that are used in lots of reports.
For example they have some excel files that are 100 000 rows and 30 columns(about 15-20mb each), in 20 of those 30 columns there are formulas (mostly vlookups), the formulas point to another file ( a support file ) that is full of info , it has 30 sheets and some sheets have 30-40 columns. The procedure is like this : They extract an excel file with 10 columns and 100 000 rows from a webapp, they copy and paste that data in the file full of formulas and they wait 2-3 hours for the calculations to be complete. They upload that file to the webapp. They have to make 20-30 of those files monthly.
Computers are always stuck with excel computing and many times not responding or crashing.
I want to help and resolve this situation but I need some help.
My first idea was to split the large file in 10-20 smaller files and run the formulas on them and after that add the result in one large file, all of that using some vba macros. But when I was half way with my idea I found out that it was pointless because of all those vlookups it will take almost the same time maybe more...
My second idea was to design a database but I don't know where to start with that ... my SQL skills are kinda limited to some simple queries from my last job where I was using PLSQL for some selects, where , like, between, etc basic queries.
I would appreciate some help because I want to save the day here and be the hero for once.
Thank you very much.

Originally Posted by scottiex

1) have you sorted our data before running the vlookup on it? (then changed the vlookup type)?
double vlookup if needed (https://www.powerusersoftwares.com/s...-VLOOKUP-trick)
2) have you considered using access for the data?
3) Are you running the formula on closed files maybe see if it is faster open?
4) What if you copy the sheets or data you need from the sheets temporarily to your formula sheet (so you are working within a single workbook or sheet) then delete them afterwards?

Thanks for your reply scottiex,

1) The data was not sorted at all ( what do you mean exactly by sorted ? like from A-Z ? )
I will look into double vlookup method.
2) They don't want to use access... I asked.
3) Running on closed files that are on a server.
4) I could try that but I don't think it will improve by much the calculation times.
I tried copying the files locally and the result was the same, even with the files open.

Originally Posted by FDibbins

(title is not really that good, but not sure what else to use)
Can you show some examples of the formulas that are being used?

=IFERROR(IFERROR(VLOOKUP(W:W,|network location/workbook/sheet |$A:$K,11,0),VLOOKUP(W:W,,|network location/workbook/sheet |$B:$D,3,0)),"Other")

Originally Posted by scottiex

1) Yes sort in the normal way. then when you do the vlookup with True as the last parameter it will work and be much more efficient because it/you will know any values below any entry are equal to or greater than that entry (alpha and number). depending on your data there may be ways to do this search even more efficiently.

4) I mean copying the data into the workbook itself.
I think you should try it - I have some files that run on external links and even a little bit of vlookups severely slow down the file.
It is a very easy test - just merge the two workbooks together and see what the calculation speed is without any external links.

If you uploaded an example (desensitized) maybe we would have other ideas - but then again I suspect what I have suggested here is enough for it to go reasonably fast.

I followed your advice and it turned out great, i'm almost there.

What I did :

1. I copied all the files locally: - The extracted data ,single sheet with 95000 rows and 8 columns
- The Formulas file, single sheet with the first 8 columns blank followed by 30 columns of formulas like the example above.
- The Support file, 30 sheet workbook with useful info the average size is 40 rows 30 columns per sheet.
2. I copied the extracted data into the formula sheet and then copied that sheet at the end of the Support file.
All runs A LOT faster and I mean A LOT, the only thing slowing the file down is one formula, a MAXIF formula and I can't find a replacement.
The formula looks like this =MAXIFS(J:J,I:I,I2)
On J is the week number and on I is the fiscal month, formula returns the last week number in that month
for example for January returns 4 for Feb returns 8 for Mar returns 13 for April 17 etc.
If I could change that formula somehow to run smoother that would be great the file would run in almost 5 minutes I think.

Thanks again for the help. Now I want to come up with some kind of macro to copy all those 20 files ( 90k rows each ) and do the same process and save the result sheet in a location with CSV format.
Then I will be the hero.
Yey

Originally Posted by scottiex

For the maxifs statement -
Are you saying you want the ISO week numbers where the week containing the 4th of jan is the 1st week?

The formula should return the last fiscal week in that month.
I think it looks at the month and the week number in that month and picks the largest number.
I couldn't manage to get a result from your formula ( round down((1/next month/year -4/1/year)/7,0) ) but I'm still trying.
I don't really know what ISO week is but maybe that's it.