## Tuesday, February 11, 2014

### yogi_Calculate The Most Recent Price Of Specified Products From Each Unique Supplier Within A Specified Range Of Dates State=1

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-12-2014
post by Yeong question by Daniel (https://productforums.google.com/forum/#!mydiscussions/docs/XCV1jmOcmrs)

Hi Yogi

I've got this table (it is just an example):

 ID Supplier Price Product State Date 1 A 25 product-1 1 01/01/2012 2 C 50 product-1 1 02/01/2014 3 B 200 product-1 0 01/01/2013 4 B 350 product-1 1 02/01/2014 5 B 250 product-1 1 01/01/2013 6 B 300 product-1 1 02/01/2013 7 A 30 product-1 0 02/01/2013 8 C 500 product-1 1 01/02/2014 9 A 40 product-1 1 01/01/2013 10 D 700 product-1 1 01/01/2013 11 A 200 product-2 1 30/01/2014 12 C 500 product-2 1 30/01/2014 13 B 120 product-2 1 30/01/2014 14 B 120 product-2 0 30/01/2014 15 A 250 product-3 1 30/01/2014

And I'm trying to find a one cell formula to calculate:
The most recent price from each Supplier (unique(supplier)) within a range of dates (for example, 600 days from today), currently available (state=1) of a certain product (in this case "product-1").

The result would be: 500, 350, 40, 700

I tried this
=QUERY(A4:F18, "select A,B where (D = '"&H2&"' or D = '"&H3&"') and F >= date '"& text(today()- I2,"yyyy-MM-dd") &"' and E = 1 order by F desc")

And this query (H4) gives me all except for the UNIQUE value per Supplier.
I though that maybe, with the array of the IDs (in cell H4) we could go through the table again and search for the UNIQUE suppliers (respecting all the previous filters (also the order)) and retrieve back the filtered IDs.