Index Match vs D Function to summarize Data

Hello,
I have a project that has several worksheets of "summarized" data all pulled from one worksheet. The data worksheet is run through an external program and pasted into the workbook to update the information.

To this point I have used tons of index match array formula's to pull the information but I've found this to be extremely inefficient because there are hundreds if not thousands of formulas, and it takes several minutes to update. The file is also around 3MB which is way too big.

I was thinking about using VBA to simplify the worksheets, but then I came across an article about D_Functions and I'm thinking they may be the solution to my problems.

However there is one catch...I'm not sure how I would build these formulas to work in my worksheets. I understand the principle behind the D_Functions, but I can't seem to grasp the concept of integration and making it work in my scenario.

I've attached an example of this workbook, it has most of the forumla's deleted out to reduce the file size, but it should give you an idea of what I'm doing. Once you open it you'll have to unhide some columns to get to the nitty gritty of the sheet.

Feel free to take a gander...I'm open to any suggestions you may have at this point (except to trash the whole project hehehehe ). If you find a solution please explain the concept behind it. I really want to learn how to make this work rather than plugging in someone elses formulas.

Thanks in advance.

try to use manual calculations instead of using calculate automatically.

EDIT: most of the points I make about your file you yourself make -- I'm just elaborating on them ;-)

I've just taken a quick look at this... firstly I'd advise making sure everything's visible when you post up -- people won't want to faff about unhiding columns to see your formulae ;-)

As previously stated firstly always try to avoid recalculating the same thing over and over... the best way to explain it is using a simple example of creating a running total in Col B based on values in A... assuming data started in Row 2 -- if in each row of B you had the formula:

=SUM($A$2:$A2)

so in B10 that reads:

=SUM($A$2:$A10)

it implies your constantly recalculating the preceeding range...
it is obviously much more efficient to use the preceeding running total and just add current value, eg:

=N($B1)+$A2

such that row 10 becomes

=N($B9)+$A10

does that make sense ?

The main point I would make having looked at your file is that you're using a lot of Array formulae to essentially do multi conditional SUMIF / COUNTIFs etc... this is common though most people would use SUMPRODUCT (little more robust in terms of user error).... the greater in number you use arrays and the larger the array dimension the greater the impact on the performance of your file as a whole. I would nearly always advocate the use of helper cells to avoid the requirement for array formulae if you are otherwise required to have a lot of them (ie a few arrays is no big deal, lots of them is).

You can often (not always) use CONCATENATION in helper cells to rid the need for SUMPRODUCT/array approach.

For ex... let's say I had a table of 3 columns A:C - let's call it TABLE1
In A I have names
In B I have depts
In C I have values
The values in this table can repeat -- ie lots of the same A:B combination ... let's say the table has 1000 rows of data.

In another summary table (let's call in TABLE2) I want to list in A:C
A: name
B: dept
C: sum of values

Now the most common approach to produce C in TABLE2 is to use a SUMPRODUCT / Array approach as (pre XL07) I can't SUMIF with 1+ criteria, so I'd have

=SUMPRODUCT(--(Table1!$A$1:$A$1000=$A1),--(Table1!$B$1:$B$1000=$B1),Table1!$C$1:$C$1000)

Now if I only had 1 of the above that would be so bad... if I have lots and lots (say for 100 combinations of A & B) this would not be so good...

What I would advocate is... add a column to Table1 (D) that concatenates the important bits of info you need, in this case A:B, so Table1!D1:D1000 would have the following:

=$A1&":"&$B1

Now what this means is that in Table2 column C I no longer need to use a SUMPRODUCT/Array approach as I can now use a SUMIF

=SUMIF(Table1!$D$1:$D$1000,$A1&":"&$B1,Table1!$C$1:$C$1000)

The above formula a 100 times is nowhere near as big a deal as 100 SUMPRODUCTS/Arrays.

I hope this makes sense...

Sometimes (if not most of the time) the less elegant approach can be more efficient... sad but true.

Also, if you're not aware be wary of using Volatile functions in big files -- for more info on volatile functions/actions see:

http://www.decisionmodels.com/calcsecretsi.htm

Your file implies to me you know what you're doing so if this sounds condescending it's not meant to... others may read and find useful who may not be at your level.

Cheers

Originally Posted by ChromeDome

Originally Posted by MMULT

try to use manual calculations instead of using calculate automatically.

I've tried this, but the problem I've run into is when I give the file to others to use it seems to go back to automatic. I've been trying to figure out a vba procedure to turn off calculation then add a macro attached to a button to run the calculation, but have been unsuccessful so far.

I recommend not using manual calculation especially on files that are being distributed to other users - at some point it is highly likely that someone will forget to turn the calculation back on & then there is huge potential/risk for inaccurate data to be relied on...
(My understanding is that Excel's calculation mode is initially set by the first workbook that is opened & I think it can is saved with each workbook that is saved while the calculation is set to manual.)


Also, I've only had a quick glance at the file but is there anyway that you could whip up a Pivot Table (PT) that does what you need?
I'm guessing it's unlikely because of the range of combinations & your desired/current layout but I've attached an example* which you can have a play with...

*I've deleted some data & some of the other sheets because it wasn't until after I uploaded that I realised that attached xls files can be larger than 100kb (when I'd already zipped it to minimise the attachment size).

Note: if you were to use a PT approach, I suggest 1) using a dynamic named range as the PT source, 2) basing sucessive PT's off the original PT cache, & 3) putting some sort of macro in the file to reformat the column widths as needed after the PT's are updated.

hth
Rob