Excel suddenly extremely slow

[duffynmu]

We have a spreadsheet that pulls in data from multiple sources (Linked tables to JD Edwards software, ODBC to SQL Server) that last week took between 5 and 10 minutes.
This week it has suddenly started taking about 80 minutes. We've had multiple people try it, with the same results.

I've stepped through the vba code that it is executing, and I find 10 statements that are doing VLOOKUP's from one tab to another tab. In one of them it is looping through a tab with about 40k rows, looking up values against a tab that has just over 200k rows. In the other 9, it is looping through the tab with 200k rows, looking for a match in the tab that has 40k rows. It takes each vlookup statement between 5 and 10 minutes to execute today; leading to the 80 minutes total.

What I can't understand is what would have changed between last week and this week to cause this massive change in performance. We are using Office 365, the Excel Application.Version returns 16.0. One user is running Windows 10, the other two are using Windows 7 Enterprise. All systems have 8 gb RAM.

One thing that confuses me is the range of 200k rows is not sorted by the column that the vlookup is matching (with EXACT setting). I've added code in the script to resort the sheet by that column, and it still takes 80 minutes to run.

I've eliminated network issues, locking issues (we are accessing an Access 2016 database that contains the linked tables). I've put both files (spreadsheet and database) on my C: drive, and on our network drive. No difference - still 80 minutes.

None of our other spreadsheets seem to be having this issue (at least nobody has complained about the times).

This is one of the statements that is choking: SALESEOD = # of rows that have data, it is looking in the 200k tab; Master has 40k rows. The column contains the results of two columns combined (quote# - part#):

Range("CE6:CE" & bb).Formula = "=IFERROR(VLOOKUP(CD6,Master!$BV$21:$BW$" & SALESEOD & ",2,FALSE),0)"

Does anyone have any thoughts/suggestions?

Thanks
Steve

[Olly]

Initial thought, with datasets that size, is to rebuild using Power Pivot, instead of running thsouands of vlookups.

[duffynmu]

Apparently the issue is that someone changed the start date to 2011, instead of 2016. I was told it was always 2011, but that was not the case.
Sorry for the wasted time... although if I get a chance I'll look into the Power Pivot product to see if it would make my life better going forward. If I can convince IT to let me install it...

[Olly]

If you're using Excel 2016 via Office 365 (as your profile indicates) then you already have it...

[CK76]

Only ProPlus subscription comes with PowerPivot (and few of Enterprise subscription). PowerQuery (Get & Transform) on the other hand, comes with every subscription of Office 365 (albeit with less connectors).

[duffynmu]

Well, that's fortunate, cuz our IT is very stingy about getting us tools...
Thanks! Now to figure out how to use it... a rainy day project...
But we have a lot of spreadsheets that use a ton of vlookups, and run for 10-20 minutes. If I could streamline those I'd be a hero!

[CK76]

To speedup VLOOKUP there are few ways to go about it.

1. Query data using PowerQuery and do lookup within it. Load only the result back to sheet or to data model.
2. Query SQL using VBA, load result to array. Do transformation/lookup operation in memory.
3. After data is loaded, use VBA to do look up.

See link for an interesting read on Lookup methods and speed.
http://analystcave.com/excel-vlookup...l-performance/

[duffynmu]

I've bookmarked that article on lookup methods for more detailed reading... it is interesting... thanks for the tips!