How to consolidate and sum data

[mfreund]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a worksheet that is an export from a relational database, consisting of a series of events for a group of individuals with multiple events for each individual. The table lists each event on a separate line with a unique identifier for the individual as one of the columns of the data.

I would like to summarize one of the fields (duration of event) by individual, so that I get the sum (total duration of all events attended) for each individual.

I tried playing with the Data: Consolidate menu item, but I can't find any reference to it in the Help document or on this website.

(This is one of the infuriating things about how Microsoft fails to support the Mac community -- providing software that doesn't even include full documentation!)

As a result, I have no idea if Data: Consolidate is the right function to use, or if there is a better way to accomplish what I'm looking to do. I suspect Pivot Tables might be another, but I have not been able to figure out how to get them to work.

Anybody have a suggestion? Or at least a place where I can read some documentation on Data: Consolidate?

 

[Carl Witthoft]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have a worksheet that is an export from a relational database, consisting
of a series of events for a group of individuals with multiple events for
each individual. The table lists each event on a separate line with a unique
identifier for the individual as one of the columns of the data.

I would like to summarize one of the fields (duration of event) by
individual, so that I get the sum (total duration of all events attended) for
each individual.

I tried playing with the Data: Consolidate menu item, but I can't find any
reference to it in the Help document or on this website.

(This is one of the infuriating things about how Microsoft fails to support
the Mac community -- providing software that doesn't even include full
documentation!)

If you think that's a Mac-centric problem, you haven't looked at the
Windows "documentation".


Anyway, read up on SUMIF. I can't tell from your description but it
sounds like that's what you want.

As they say in another data analysis group I frequent, " please supply
commented, minimal, self-contained, reproducible code."

 

[CyberTaz]

Consolidate isn't what you want ‹ it's primarily for consolidating or
summarizing similar data stored in separate locations, such as on different
sheets or in several workbooks.

It's hard to tell what might work best in your case because you don't offer
a very rich description of the data. [i.e., is the "duration" stored as a
Number or what?] Perhaps SUMIF, SUMIFS or DSUM would do the trick. You may
also be able to get the results you want by using the Data> Subtotals
feature or a Pivot Table. As far as why you're having trouble with a Pivot
Table I can't say ‹ again, because you don't describe your data & its layout
well enough nor do you expand on what the difficulty is with the efforts
you've made previously.

Regards |:>)
Bob Jones
[MVP] Office:Mac