Help me prioritize the people I bug for money

May 1, 2007 12:49 PM

Excel-filter: I have a spreadsheet with a list of donations made over the past year. Unfortunately, when an individual has donated multiple times within a year there is a new row for each donation. Fortunately, that individual is assigned the same unique identifier. How do I make it so that each individual has one row with total donations for the year?

In other words, I want to go from this:

001, John Smith, $10
001, John Smith, $15
001, John Smith, $30
002, Jane Doe, $10
002, Jane Doe, $12

to this:

001, John Smith, $55
002, Jane Doe, $22

posted by ewiar to Computers & Internet (6 answers total)

Best answer: You want to make a Pivot Table
posted by nathan_teske at 12:53 PM on May 1, 2007 [1 favorite]

Response by poster: And done. Thanks nathan.
posted by ewiar at 12:57 PM on May 1, 2007

If you happen to be using the Raiser's Edge and exporting the data to Excel, next time, export the summary information for gifts.
posted by nomad73 at 1:45 PM on May 1, 2007

Alternatively, you could use the SUMIF function, such as:

=SUMIF(A:A,A1,C:C)

(presuming your identifier is in column A, starting at 1)

I know you've got your answer, but pivot tables annoy me, so if someone else reads this....
posted by pompomtom at 3:40 PM on May 1, 2007

Glad you got it worked out but as a DB admin, I can't stress enough moving to an access database at the least. I use Raisers Edge, which works great, well for most stuff, but it may be cost prohibitive for your org.

Good Luck
posted by imjosh at 6:09 PM on May 1, 2007

imjosh is right. Use a relational database for these issues. I first learned how to work with access after facing the exact same problem in a 65 page spreadsheet. After suggesting Access to the boss and then learning how to do it, I became the de facto database admin and got a 20% raise. Off of some stupid trick I saw in Access for Dummies, our organization won a nation-wide award.
posted by Ironmouth at 8:35 PM on May 1, 2007