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

                                         Google Spreadsheet   Post  #1525
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):

IDSupplierPriceProductStateDate
1A25product-1101/01/2012
2C50product-1102/01/2014
3B200product-1001/01/2013
4B350product-1102/01/2014
5B250product-1101/01/2013
6B300product-1102/01/2013
7A30product-1002/01/2013
8C500product-1101/02/2014
9A40product-1101/01/2013
10D700product-1101/01/2013
11A200product-2130/01/2014
12C500product-2130/01/2014
13B120product-2130/01/2014
14B120product-2030/01/2014
15A250product-3130/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.

I had some more evolutions:
in K3:K7 and L4:L7

The results are now showing at but achieved in a multiple rows and semi-manual process.
So I would like to have this in a one-cell-query:
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

Thank you very much for your time in advance!!!
Cheers,
Daniel
----------------------------------------------------------------------------------------------------------------------------------------------------