Sumif, dsum, query, something else? What's faster?

[codyswan]

I have some very large files (60-100 meg) where I want to get counts, sums, and averages for many sets of multiple criteria. I've previously used dsums, but each dsum takes up two rows and thus copying and editing formulas isn't as convenient. The dsum variety takes about 2 hours to calculate thousands of different dsums on a dataset. What is a faster alternative? Will sumifs take about the same amount of time and just be easier to deal with?

Thanx

[Mark W.]

Have you tried using PivotTables?

[codyswan]

Yes, I tried pivot tables. Unfortunately, many of the criteria have wildcard characters. One company may show up in the database as ABC Corp, ABC Corporation, ABC, ABC Inc, etc. The pivot tables treated them as different categories. Since I'm doing crosstabulations of at least two variables, I didn't see an efficient way of collapsing the categories.

[Mark W.]

On 2002-03-28 10:47, codyswan wrote:
Yes, I tried pivot tables. Unfortunately, many of the criteria have wildcard characters. One company may show up in the database as ABC Corp, ABC Corporation, ABC, ABC Inc, etc. The pivot tables treated them as different categories. Since I'm doing crosstabulations of at least two variables, I didn't see an efficient way of collapsing the categories.

Create a Grouped item or "map" all these variants to one name in separate column of your Database.

[Dave Hawley]

Hi codyswan

I find you problem very interesting, in particular the fact you say that the Database functions are very slow to recalculate. I use this functions a lot with worksheets containing thousands of rows by 20 or so columns and have never run into a speed issue. Are you sure you do not have other functions that would be causing this, array formulas are normally a prime cause.

You also mention that editing them is awkward. I find these functions extremely convenient for this, hence my reason for using them. They do take a bit longer to set up (which is why so many people avoid them) but the benefit far outweighs this.


I have a Workbook you can download here:
http://www.ozgrid.com/download/default.htm that shows how these functions can be used in a very convenient way.
"DFunctionsWithValidation.zip" is the Workbook.

[codyswan]

The database is about 40,000 records long by 25 columns. There are about 200,000 crosstabs to calculate on each database. Each cell in the crosstab has two conditions. Sometimes the conditions include wildcards.

I don't have any array formulas.

To answer certain questions, I have to go back in and write new dsums and dcounts.

I'm going to try pivot tables again with collapsed categories and see how long it takes.

thanks

[Yogi Anand]

Hi codyswan:
You have already got some valuable insight from the masters here. Just a thought -- have you tried switching to MANUAL CALC, and calculate just what you need rather than calculate everything even if you want to calculate something very specific.

[codyswan]

Yes, Yogi, I've set the recalc to manual and only do a worksheet recalc (shift F9).

The pivottable is looking promising, but I'm running into problems with it having too many columns. Somewhere in there is a setting to get the pivottable to wrap.

[Yogi Anand]

Hi codyswan:
How about going down to calculating at a range-level or even cell level. In the past when I was working with relatively large Lotus files, I would have CALC turned to manual, then I would force a calc on a cell by copying over itself. Excel does not facilitate that one can achieve that facility througn a one-liner in VBA.

[codyswan]

I have it set so that the formulas are text when I bring in data. Then, I removed the text marker and they become formulas. To get a recalc of an individual formula, I just delete edit the cell, put in and delete a space on the end and hit enter. It recalcs just that cell.