Thursday, September 27, 2012

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.

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?
----
----
....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