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

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

Here is the link:

https://docs.google.com/spreadsheets/d/1UCGsCQJX3QHJIFXE7q-hNkZzXprwhi6DPF47NNz7myc/edit?usp=sharing

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

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