Counting non-recurring/unique entries in a complex spreadsheet

**SaulGold** · 09-02-2010, 12:18 PM

I run nonprofit organization and we accept both one-time and recurring donations. Each month I update a spreadsheet with our most recent income data. The spreadsheet is laid out like this:

First Name, Last Name, Email Address, Date of Donation, Amount, Number of Installments

Let's say John Smith pledged $20 per month for a year on 9/1/10; his donation on 9/1/10 would appear just as you'd imagine. But on 10/1/10, his $20 donation would appear again, this time without the number of installments listed in the last column (it would just be left blank). Same would be true on 11/1/10, and so on.

Now let say that Jane Doe made a one-time contribution of $25 on 9/1; the last column in the entry is left blank because the donation does not recur.

Here's how the above example would look on my spreadsheet:

John, Smith, john@gmail.com, 9/1/10, $20, 12
Jane, Doe, jane@gmail.com, 9/1/10, $25
John, Smith, john@gmail.com, 10/1/10, $20
John, Smith, john@gmail.com, 11/1/10, $20

You get the idea. I want to be able to count the number of new donors in a given month. Using the example above, there were two new donors in September and zero new donors in October and November. The actual spreadsheet is hundreds of rows long and spans the past two years.

Bonus question: I'm not sure whether this one is possible, but it would be fabulous if I could either 1) count down the number of installments a donor has left, or 2) indicate a "Date of Final Installment" as a column beside the number of installments. Any idea how I could do one or both of those?

Thanks very much for your time.

**mrice** · 09-04-2010, 09:41 AM

Assuming that your sheet has entries in chronological order and is the first sheet in the workbook, you can populate a blank second sheet with this macro.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

**shg** · 09-04-2010, 12:44 PM

A macro seems like overkill for this.

Please Login or Register  to view this content.

The formula in G2 and down is

=IF(COUNTIF(C$1:C2, C2) =1, "New", "")

Then you can use a pivot table or filter.

**Cheeky Charlie** · 09-04-2010, 12:51 PM

A Amcro seems like overkill for this.

This was my thought exactly (apart from the spelling, my thoughts are spelled properly...)

Highly recommend a pivot - it will pop out your 'bonus questions' (and more) in seconds

**DonkeyOte** · 09-04-2010, 01:13 PM

I was looking at this but sensed the ultimate can of worms...

Using ex. of Jane Doe:

In Sep-10 she opts for a stand alone donation
In Jan-11 she opts to take up a 12 month donation - so one new transaction in Jan-11 and 11 non-new transactions Feb-Dec 11
In Feb-12 she opts for another stand alone donation

To determine whether or not the Feb-12 transaction is new (or any transaction for that matter) I believe you must conduct the following tests:

1. is this first instance of name or is instalment # > 0

If either holds true then must be new

2. where neither new name nor instalment > 0 you must then find last instalment based transaction for the benefactor

If such a transaction does not exist then you know it's new (new stand alone donation)

If such a transaction does exist then you must determine as to whether or not all instalments have been completed for that prior instalment transaction.

If the answer is yes then this must be a new one off donation - if no then it's an instalment.

Only then can you be sure it's new - at least that's my interpretation.

**Cheeky Charlie** · 09-04-2010, 01:51 PM

In Sep-10 she opts for a stand alone duration
In Jan-11 she opts to take up a 12 month donation - so one new transaction in Jan-11 and 11 non-new transactions Feb-Dec 11
In Feb-12 she opts for another stand alone duration

duration ==> donation

(DO feel free to hose this post once read)

**DonkeyOte** · 09-04-2010, 02:01 PM

@CC - thanks - modified !

I no longer have the "power" to modify any content other than that in my own posts ... hopefully shg can "can" this post and yours (credited in the footnotes for posterity)