"Adding" rows from one workbook to another, and expanding workbooks by row

[The Third Man]

I've been asked to put together a "one-button" report in Excel, and I'm
about at my wit's end in trying to figure out how to do it! I'm not
even sure this can be done, so I've finally come here.

I've put together three campaign reports for my university using
hyperlinks. Essentially, the reports work by downloading information
from our proprietary database into a source file, then preparing a
destination file to sort the data the way we want. Now I've been asked
to prepare a fourth report: the difference is that the data is not
summed like the other three, but rather re-sorted into rows.

So, the source file is going to look like this:

Jones $10,000 Business
Smith $20,000 Science
Roy $5,000 Arts
Johns $50,000 Business
Thomas $20,000 Arts
White $4,000 Business

And the destination file is supposed to look like this:

Science (1 gift(s)) Total: $20,000
Smith $20,000 Science

Arts (2 gifts(s)) Total: $25,000
Roy $5,000 Arts
Thomas $20,000 Arts

Business (3 gifts(s)) Total: $64,000
Smith $10,000 Business
Johns $50,000 Business
White $4,000 Business

The question is: Can Excel even do this? And, if it can, how?

I know that these types of operations can be done in Crystal Reports or
similar programs, but for a number of reasons I'd really like to keep
this in Excel.

Thanks, The Third Man

 

[tim m]

Have you experimented with Pivot tables to arrange and calculate your data?

 

[The Third Man]

No, I haven't tried Pivot Tables. I don't know much about them, but
I'll take a look. Thanks.