Index Match Processing Stall

[aritsa]

I am using Excel. Unfortunately I am using it on Mac.

One of the many issues that I am repeatedly experiencing is long processing times. In most cases, this leads to stalls, shutdowns, and restarts.

I am not able to use Macros on large sets of data.

Currently I am attempting the following Index Match combination:

{=INDEX(ADP!$C:$C,MATCH($B1&"EMPLOYEE_IDENTIFIER",ADP!$A:$A&ADP!$I:$I,0))}

The sheet "ADP" can contain up to 100k lines of which less than 10k contain "Employee_Identifier".

Is there any way to process this much data without crashing my system?

[loginjmor]

Hi -

Here are a few ideas:

Try not to use column references in your formulas such as $C:$C. These force Excel to evaluate every single cell in the column, even if most of them are empty. Specify a defined range (e.g., $C$1:$C$10000)

Also, limit the number of array formulas. Those are HUGE memory hogs. If you can figure out other ways to get the same functionality without using an array formula (such as adding helper columns) then replace those.

Try not to do everything in one spreadsheet. If possible, break your data set up into separate files (such as by year or some other logical division). Then you can reduce the amount of data you have to go through.

If your formulas have volatile functions in them (e.g., TODAY(), NOW(), OFFSET(), INDIRECT(), etc.), they all have to recalculate whether the cells they reference have changed or not. Try to limit those to just a very few instances in your spreadsheet. If they are in a formula you copy 1,000 times, performance may suffer.

There are a few others you can google. It just depends on the structure of your data and how you are using it. Obviously if your computer crashes all the time, it's not very useful so you might as well accept the idea you have to change some of these things. Hope these help.

[FDibbins]

Instead of this ARRAY formula...
=INDEX(ADP!$C:$C,MATCH($B1&"EMPLOYEE_IDENTIFIER",ADP!$A:$A&ADP!$I:$I,0))
Try this regular formula...
=INDEX(ADP!$C:$C,MATCH($B1&"EMPLOYEE_IDENTIFIER",index(ADP!$A:$A&ADP!$I:$I,0),0))

Try not to do everything in one spreadsheet. If possible, break your data set up into separate files

I would not necessarily agree with that. You may end up needing more processing power to access another file, plus you eliminate teh ability to use functions like sumifS(), countifS() etc
Once the data is there, it's there, and will take the same amount of effort to calc with.

1 option to consider would be using Pivot Tables.

If your formulas have volatile functions in them (e.g., TODAY(), NOW(), OFFSET(), INDIRECT(), etc.), they all have to recalculate whether the cells they reference have changed or not. Try to limit those to just a very few instances in your spreadsheet. If they are in a formula you copy 1,000 times, performance may suffer.

Totally agree. 1 way around that is to just have TODAY() in it's own cell and reference it

[rorya]

I'd agree with the helper column comment. Adding a new column (say column Z) to the ADP sheet that uses the formula:
=A2&I2
copied down, and then use a regular formula:
=INDEX(ADP!$C:$C,MATCH($B1&"EMPLOYEE_IDENTIFIER",ADP!$Z:$Z,0))

If you must use array formulas, do not use them on entire columns - limit the ranges to only the actual data.

[loginjmor]

FDibbins has a good point on referencing other files. However, if you have 10 years of data in your file and you're only analyzing the last 3 or 5 years on a regular basis, consider pulling some out. Or if it's a list of accounts and say 10% are inactive, move those to a separate file. Excel tries to keep track of inter-relationships between cells so it only recalculates the ones and change. But once you get over a certain number of cells (I think the number is around 65,000 but don't quote me on that) Excel can't keep track of all the relationships so it just recalculates everything each time any change is made. So, size does matter. If you can reduce file size, do it. You can always use SUMPRODUCT if you have to to access data in the closed files occasionally. But be warned, SUMPRODUCT is a memory hog even more so than SUMIFS or COUNTIFS. So if you are going to access the data in other files regularly, splitting data out to separate files won't work.