Need Help Optimizing an Array Formula (Simplified and Updated)

PLEASE SEE POST #13 FOR AN UPDATED VERSION OF MY QUESTION

Hello,
I recently had help creating the formula below at this very forum. I was very fortunate to find someone to help me code an array with so many parts but due to the amount of data I wish to evaluate the formula can take up to 45 minutes to an hour to calculate. I’m hoping that someone can take a look at the array and improve upon it and hopefully make it calculate much faster.

The rules of the formula and the formula it’s self can be found below.

Please Login or Register  to view this content.

RULES
Step 1: Match the values of rows Number Combos #1 and #2 columns “J” and “K”.

Example:
The formula would find all rows that contain the same value for column “J” [11111] and that contain the same value for column “K” [222].

Step 2: The formula would then determine if there are Win, Loss, or Break Even in column “L” for each matching value, if there are any, found between columns “J” and “K”.

Step 3: After all matches were found and the formula determined if there were Win, Loss, and/or Break Even present if so the following rules would be applied for the different variations:

If 1 Win (only) =
All TRUE Values from columns [M to U] would be the result in columns [V to AD] in order of appearance.

If 2+ Win (only) =
All of the rows between them that had TRUE values in the same column for each Win from columns [M to U] would be the result in columns [V to AD] in order of appearance.

If 1+ Loss (only) (AND/OR) 1+ Break Even (only) =
Column “J” Number Combo #1 would be the result for [column V] (1st Instance)/Column “K” Number Combo #2 would be the result for the [column W] (2nd Instance)

If Mix Between 1 Win (only) (AND) 1+ Loss (only) (AND/OR) 1+ Break Even (only) =
1 Win (only): All TRUE Values from columns [M to U] would be the result in columns [V to AD] in order of appearance. (AND)
1+ Loss/1+ Break Even: Would list in order of appearance the instances were Win was TRUE and Loss or Break Even were FALSE

If Mix Between 2+ Win (only) (AND) 1+ Loss (only) (AND/OR) 1+ Break Even (only) =
2+ Win (only): All of the rows between them that had TRUE values in the same column for each Win from columns [M to U] would be the result in columns [V to AD] in order of appearance.
(AND)
1+ Loss/1+ Break Even: Would list in order of appearance the instances were ALL 2+ Win were TRUE and Loss or Break Even were FALSE

More Information
L17 contains the Win and L11 and L15 contain the Losses and I need the formula to find ALL of the cells where the Losses are False but the Wins are True.

So in other words both Losses don't have to be False in order for them to be listed as a result but all of the Wins have to be the same in order for the Losses to apply but the rule isn't the same for the Wins.

So even though the N11 Loss is True the result for the N15 Loss should still be listed because it's False and different from the N17 True. That's why the first instance for V15 should be "B".


I have attached spreadsheet for a better understanding.


Thank you in advance for any and all help provided.

it seems that, part of building these big, ugly array formulas is to avoid using helper cells/ranges. One consequence I see in this approach is that we sometimes create extra effort for the computer in the process. For example, in your formula, there is a TRANSPOSE(ROW(range)^0) section, whose sole purpose is to calculate a horizontal array of 1's. I don't really know how long it takes a modern processor to a) look up the row number, b) raise it to the 0th power to get 1, and c) transpose that vertical array to a horizontal array, but it will take some time. Multiply that by the number of incidences of the formula across a very large data set, and this kind of duplication of effort can add to the processing time. I haven't looked at your formulas in more detail, so there may be other examples of duplication of effort. My first thought is to explore whether using helper cells/ranges to minimize this kind of duplication of effort will reduce the computation time.


A discussion from some time ago with an example of how this duplication of effort significantly increased computation time. http://www.excelforum.com/excel-form...-and-cons.html

I'm sorry, but I'm not very good with these kind of array formulas either.

The next observation I make is that step one is simply a filtering step. I notice that you have it set up to use the autofilter tool. Perhaps by extending the idea of the filter we can improve the performance. For example, would the spreadsheet be adaptable to a situation where each J,K combination is in its own tab. If the pair sort breakdown listed is accurate, there are only 11 possible combinations of J,K values. If these were each in their own tab, then we could perhaps decrease the time of a calculation event by breaking the database up into smaller chunks.

Reading between the lines, I expect you are seeing these long calculation events when you add an entry or make a change to an entry. When you structure a spreadsheet/database like this where all of the calculations are based on the entire database, when you make a change anywhere in the database, Excel sees the dependency as "I need to recalculate the entire block of array formulas". In reality, a change/addition to the database probably only effects about 10% of the calculated results. If we can restructure the spreadsheet/calculation so that Excel can see that you have made a change that will only effect the 11111,111 combinations (for example) so Excel can choose to only recalculate those results, we may be able to reduce the time for a calculation event.

I'm not good at these kind of "database" calculations, so I don't know if I have any good recommendations. My first thought was to separate the big database into 11 smaller databases -- one for each J,K combination. Then, when I needed to add/change something in the 11111,222 combination, it would be done in that tab and Excel would only need to recalculate that part of the database. I expect this will dramatically reduce the time for a calculation event, but it might make maintaining the database much more difficult. Somewhere in this might need to be a discussion about balancing "I hate waiting for Excel to perform these long calculation events" against "I wish this database were easier to add/change/maintain."

Would it be enough to set calculation to manual? Then you could make your changes/additions without interruption. Then, just as you are leaving for lunch, press F9 to initiate the calculate event, and Excel should be nearly done by the time you get back from lunch.

Thread closed as OP request.

He wants to post in the Commercial Services Sub forum.