Using an ARRAY_CONSTRAIN formula we want to capture the latest 9 records (rows) over a period of nonconsecutive dates.
=ARRAY_CONSTRAIN(sort(vlookup(query({row(EBPP!D:D),sort(EBPP!A:E)},Q15,0),
{row(EBPP!D:D),sort(EBPP!A:E)},{2,3,4,5,6},0),4,0),R8,5)
Points to consider:
Q15 holds the queryR8 will hold the part we need for the dates.Please have a look at the image.
Spreadsheet added.
={A1:E1; QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"where Col4 <="&INDEX(QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"select Col4
order by Col4 desc"), 2, 1)&"
and Col4 >="&INDEX(UNIQUE(QUERY(SORT(VLOOKUP(QUERY({ROW(D:D), SORT(A:E)}, K1, 0),
{ROW(D:D), SORT(A:E)}, {2, 3, 4, 5, 6}, 0), 4, 0),
"select Col4
order by Col4 desc")), 5, 1)&"
limit 9", 0)}
={A1:E1; QUERY(QUERY({A2:E}, K1, 0),
"where Col4 <= date'"&TEXT(MAX(QUERY({A2:E},
"select Col4
order by Col4 desc", 0)),"yyyy-mm-dd")&"'
and Col4 >= date'"&TEXT(TO_DATE(INDEX(UNIQUE(QUERY({A2:E},
"select Col4
order by Col4 desc", 0)), R9, 1)), "yyyy-mm-dd")&"'
limit "&R8, 0)}
Dates difference hits 10. It will not give more results no matter how high it gets. Also. Could it still work using an ARRAY_CONSTRAIN as we started it originally and if so, how?
– marikamitsos
May 21 at 5:35
limit 10 and works fine. as for the VALUE error when changing 5 to 10 - thats kind of normal coz the whole data sample does not have 9 unique dates. and no. solving this only with arrayconstrain is not doable
– user0
May 21 at 8:25
E there is a total of 30 recorded dates/records. (30 dates - duplicate dates)=22 dates. So to give an example to the question. "What should we change so we could have results from the latest 15 dates limited to 25 records?" These are our 2 variables (unique dates and records) that should be taken out of the formula (each to its own cell) as to be easily altered. Hence in the original question "Cell Q15 holds the query" and "Cell R8 will hold the part we need for the dates".
– marikamitsos
May 21 at 18:45
We finally came up with an eloquent solution using the ARRAY_CONSTRAIN, SORT, QUERY functions.
This is the final formula, small, flexible (both variables out of the formula), row expandable, robust (works with empty cells):
=ARRAY_CONSTRAIN(SORT(
QUERY(A2:E, "select * where D >= date '" & text(J21, "yyyy-mm-dd") & "'"),
4,FALSE,5,),M21,5)
You can try and play with it on the spreadsheet.
RESULTSis editable so you can use it for testing. – marikamitsos May 21 at 0:39