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