Wednesday, October 15, 2014

yogi_Keep Only The Latest Instance Of A Property_Number In Column A Based On Certified Date

                Google Spreadsheet   Post  #1795
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Oct-15-2014
post by  Paul Gilg:
Need help with formula to remove duplicates using the date range as a criteria

I have been all over Google forum for this answer but seem unable to find anything.  Please assist if possible.

Here is what I am trying to accomplish:

I have compiled all my data into a Master tab.  From there I ran a query to find columns based within a date range (=ArrayFormula(QUERY(Master!D:P,"Select D, E, M, P Where G = 'Search' AND M > DATE '2013-06-01' AND M < DATE '2014-08-31' order by D asc"))

That gave me the following results:

Property NumberSub-Property NumberCertified DateCertification Level
Property 16/6/2013Full
Property 2Sub-Property 18/21/2014Full
Property 36/5/2014Full
Property 45/15/2014Fail
Property 45/21/2014Full
Property 55/27/2014Full
Property 64/22/2014Denied
Property 73/24/2014Exemption
Property 84/22/2014Exemption
Property 93/24/2014Exemption
Property 103/24/2014Exemption
Property 11Sub-Property 25/7/2014Full
Property 123/28/2014Exemption
Property 128/15/2014Exemption
Property 138/6/2014Full
Property 148/15/2014Exemption
Property 158/15/2014Exemption
Property 16Sub-Property 38/20/2014Full
Property 17Sub-Property 46/6/2014Full
Property 18Sub-Property 56/16/2014Full
Property 19Sub-Property 67/17/2014Full
Property 20Sub-Property 75/6/2014Full
Property 21Sub-Property 85/1/2014Full
Property 225/7/2014Partial
Property 2211/7/2013Partial
Property 23Sub-Property 96/13/2013Fail
Property 23Sub-Property 97/9/2013Full
Property 24Sub-Property 108/28/2013Partial
Property 258/20/2013Partial
Property 256/20/2013Fail
Property 264/1/2014Denied

I am looking to write a formula that would remove the duplicates (Column A) based off the most recent certification date (Column D).  I have tried all sorts of formulas but am still not proficient at knowing how to write my own.  I have been hitting a brick wall on this and would appreciate any insight as to how to construct this formula.