more efficient consolidation formula

[Jbentley]

Hi,
Hoping for some guidance on a more efficient way to consolidate a large table fo data.

XL version 2007 Sp2.

real world data range is over 35,000 rows and 138 columns.
unfortunately re-organising the data is not really an option.

I've been using sumproduct (see attached) which takes a very long time to calculate (more than 1 hour).

Rather than me blathering on it'd be easier to look at the attached.

Apologies for thefiles size, (tredding the fine line between too much data and too little)


Any insights much appreciated.

[Jbentley]

Hi Stormseed,
Thanks for the info.
Yeah, lucky to have XL07 (though sometimes I'm not sure).

I tried VBA to recreate what I'm doing with the SUMPRODUCT formula but couldn't get it to work.
Any ideas on coding that (specific to my example file) would be great.

The data is pulled from an oracle db via object oriented sql query builder - unfortunately the row 'visit location' names and column 'visit location' names are the same dimension/datapoint, so the app naturally tries to coecre synchronisation of the dimensions (even when pivoting) hence trying to solve it in trusty Excel.

[DonkeyOte]

perhaps...

Please Login or Register  to view this content.

less elegant, more efficient.

[Jbentley]

Thanks DonkeyOte!

I'll have crack at the large file in the morning (22:55 here).

Feel I should have a witty quote from Black Adder to part with, not sure why...

Oh well. Thanks again.

I've never concatenated reference elements in formula so this is a good learning exercise in more ways than one - I'm guessing but I suppose this is easily adapted to Sum (I've other metrics to aggregate, spend, dwell time etc).

Cheers!!

[DonkeyOte]

Concatenation in conjunction with SUMIF/COUNTIF (& wildcards) is 9 times out of 10 a far more efficient method for dealing with multi conditional calculations when dealing with:

a) large volume of calcs

b) large volume of data

I have illustrated this on a few threads around here in the past but can't find links presently... as and when I do I will post them here so you can see how you can use wildcards in conjunction with SUMIF/COUNTIF to make the approach pretty flexible.

In XL2007 you do have COUNTIFS / SUMIFS which can negate need for SUMPRODUCT and concatenation based approach but in this instance they are not viable unfortunately given dimensions in use.

[JBeaucaire]

I know you said your data probably can't be changed, but looking at it I have to point out that it is 10 times longer than it probably needs to be.

All of the pertinent information is available in columns C:Q. Of the sample data, there are actually only 532 unique customer IDs. Scaling that up, maybe less than 5000 actual customers?

If the customer ID appeared ONCE each instead over and over again and column C:Q included all the places that were visited, you would have to analyze the information differently, but it would all still be there, the same conclusions can be drawn and at a fraction of the cost.

Scanning through the original data I do see there occasional variations where a customer ID appears more than once and the C:Q values aren't exactly the same (but they should be, yes?), but mostly each row contains the same info in each section. For instance, ID #1813307 appears 11 times and it's the same information. That means in your current design you're evaluating the same customer 11 times for no gain.

I've attached a sheet showing a second table off to the right where the unique IDs for the sample data are listed.

The construction of the formulas on the OutPut sheet would take a little more engineering, but based on the size of the current data layout, the new layout is worth the effort since your sheet becomes far more manageable.

If you're willing to forgo the multiple listings of each client and just use a single data line for each, indicate so, we can help you complete the other calcs based on that much shorter data set.

[DonkeyOte]

I have illustrated this on a few threads around here in the past but can't find links presently... as and when I do I will post them here so you can see how you can use wildcards in conjunction with SUMIF/COUNTIF to make the approach pretty flexible.

Here are a couple where the revised approach has had fairly significant impact on overall performance (without need to switch to Manual Calc (last resort IMO))

http://www.excelforum.com/excel-gene...or-others.html

http://www.excelforum.com/excel-gene...t-formula.html

[Jbentley]

Thanks both DonkeyOte and JBeaucaire,
Both look promising.
I'll eventually be dealing with 60k+ customers and up to 135 visit locations for each.

This is customer visits to casino gaming machines (machines aggregated by geographic locale) and, I hope, will help my product and customer relationship discussions.

[JBeaucaire]

There are formulas for everything, but if your data is going to grow THAT big, you absolutely must eliminate unnecessary duplication, don't you agree?

Evaluating the same information 11 times for one customer for no gain on the small sample, that would size up to 100s and 100s of pointless calculations if the current data setup reached 60k customers.

While you can, convert to 1 row per customer now, eliminate the unnecessary column C, then start putting together the versions of your formulas that will apply to that data format so it's done when the data starts to explode.

Cheers!