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     www.energyefficientbuild.com.   Oct-15-2014

post by  Paul Gilg:

(https://productforums.google.com/forum/#!mydiscussions/docs/V2KIPk5esSU)
Need help with formula to remove duplicates using the date range as a criteria

Hello, 

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 Number	Sub-Property Number	Certified Date	Certification Level
Property 1		6/6/2013	Full
Property 2	Sub-Property 1	8/21/2014	Full
Property 3		6/5/2014	Full
Property 4		5/15/2014	Fail
Property 4		5/21/2014	Full
Property 5		5/27/2014	Full
Property 6		4/22/2014	Denied
Property 7		3/24/2014	Exemption
Property 8		4/22/2014	Exemption
Property 9		3/24/2014	Exemption
Property 10		3/24/2014	Exemption
Property 11	Sub-Property 2	5/7/2014	Full
Property 12		3/28/2014	Exemption
Property 12		8/15/2014	Exemption
Property 13		8/6/2014	Full
Property 14		8/15/2014	Exemption
Property 15		8/15/2014	Exemption
Property 16	Sub-Property 3	8/20/2014	Full
Property 17	Sub-Property 4	6/6/2014	Full
Property 18	Sub-Property 5	6/16/2014	Full
Property 19	Sub-Property 6	7/17/2014	Full
Property 20	Sub-Property 7	5/6/2014	Full
Property 21	Sub-Property 8	5/1/2014	Full
Property 22		5/7/2014	Partial
Property 22		11/7/2013	Partial
Property 23	Sub-Property 9	6/13/2013	Fail
Property 23	Sub-Property 9	7/9/2013	Full
Property 24	Sub-Property 10	8/28/2013	Partial
Property 25		8/20/2013	Partial
Property 25		6/20/2013	Fail
Property 26		4/1/2014	Denied

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.

Thanks.

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