Counting non-recurring/unique entries in a complex spreadsheet

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.

Sub Test()
Sheets(2).Cells.Clear
Sheets(2).Cells(1, 1) = "First"
Sheets(2).Cells(1, 2) = "Last"
Sheets(2).Cells(1, 3) = "E-mail"
Sheets(2).Cells(1, 4) = "Joined"
Sheets(2).Cells(1, 5) = "Installments"
Sheets(2).Cells(1, 6) = "Made"
Sheets(2).Cells(1, 7) = "Due"

Sheets(1).Activate
For N = 2 To Cells(Rows.Count, 1).End(xlUp).Row
    If Application.CountIf(Sheets(2).Columns(3), Cells(N, 3)) = 0 Then
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(1, 0) = Cells(N, 3)
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, -2) = Cells(N, 1)
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, -1) = Cells(N, 2)
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 1) = Cells(N, 4)
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 2) = Application.Max(Cells(N, 6), 1)
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 3) = Application.CountIf(Columns(3), Cells(N, 3))
        Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 4) = Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 2) - Sheets(2).Cells(Rows.Count, 3).End(xlUp).Offset(0, 3)
    End If
Next N
End Sub

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.

A macro seems like overkill for this.

      --A-- --B--- -------C------- ----D----- -E-- ---F--- -G--
  1   First  Last       eMail         Date    Amt  Install New?
  2   John   Smith  john@gmail.com 09/01/2010 $20       12 New 
  3   Jane   Doe    jane@gmail.com 09/01/2010 $25          New 
  4   John   Smith  john@gmail.com 10/01/2010 $20              
  5   John   Smith  john@gmail.com 11/01/2010 $20

The formula in G2 and down is

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

Then you can use a pivot table or filter.

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

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.

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)

@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)

lol I feel uncomfortable with you down at my level!