yogi_List NewMembers And Quitters From Membership Roster For Years 2011 And 2012

Yogi Anand, D.Eng, P.E.      Google Spreadsheet   Post  #780  Sep 27, 2012    www.energyefficientbuild.com.

user ThNic said: (http://productforums.google.com/forum/?zx=cybaz6xii5cb#!mydiscussions/docs/rYWjd0y-M6I)

Beginner needs help on Filter / Query

Hi there,

I am a beginner of spreadsheet programming, although somewhat apt at Excel.

This is my question: I want to compare happenings in different years from the same table. Specifically: who did pay membership fees last year but not this year.

My setup is with columns that all have named ranges;

Name (select from in-cell drop-down validation list in another sheet);

Bankaccount number (vlookup from another sheet);

Date (to be entered manually);

Amount (to be entered manually);

Transaction (select from in-cell drop-down validation list in another sheet);

Reference (entered manually)

Remarks (entered manually)

Entries are either downloaded from the bankreport, shuffled around and pasted into the spreadsheet, or entered if there are few transactions.

So: for several people I have their yearly membership fee entered in the same sheet, but with a different date.

I now want to filter out those who were paying members last year, but have not renewed their membership this year. And I want just 1 list of names.

I have tried something like =filter('Fin.Transacties'!B:F; ('Fin.Transacties'!F:F="2012")+('Fin.Transacties'!F:F<>"2011")) , but that does not work, because it is the wrong syntax for what I want: it returns everything except 2011.

Also: it does not give me 1 list of names, but each entry for each year.

Who can help?

----

.... this is the link: https://docs.google.com/spreadsheet/ccc?key=0AlvMPBJLI6OCdHJPM0RuNGxVV1VaSVVUcGpLMFhqX1E

----

....I added two colums (T & U) with notes in the sheet. In fact I am after the delta of the two lists that you have generated, and represent that delta in one list:

• list 1 = names that are on the 2012 list, but not on the 2011 list. Those are the people that are new members.
• list 2 = names that are on the 2011 list, but not on the 2012 list. Those are the "quitters". 

I hope my question is clear now. If not, let me know.

------------------------------------------------------------------------------------------------------------

following is a solution to the problem for listing Newmembers and Quitters