1

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:

  • Cell Q15 holds the query
  • Cell R8 will hold the part we need for the dates.

Please have a look at the image.

latest 9 records


Spreadsheet added.

  • can you share a copy of your sheet? – user0 May 20 at 23:56
  • Sorry no. I just made up the data, made a photo and deleted it. Is there something I can explain to you? – marikamitsos May 21 at 0:00
  • 1
    @user0 Hold on. I will make one. – marikamitsos May 21 at 0:10
  • @user0 Please have a look at the spreadsheet. For the moment sheet RESULTS is editable so you can use it for testing. – marikamitsos May 21 at 0:39
1

={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)}

0


UPDATE:

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

  • Sorry it took me some time to get back to you. I was testing as well as studying the formula. It works great for my question. Still. There seems to be a glitch when the limit hits 10. Do you know why? Could you have a look at it? You can try it on the spreadsheet. – marikamitsos May 21 at 5:20
  • There is also another glitch is when the 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
  • tested with 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
  • I feel we are a bit lost about the dates range. On column 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
  • and again, this is not how it works. this is the full output from the blue data table. and you want to filter out all except last 4 unique dates and then limit the whole array to display only max of 9 rows of those 4 unique dates. therefore you need to find last date, then you need to find 4th unique date from top of the latest dates. when you have these two variables you can wrap your initial formula (from the picture) into QUERY and filter out the array (on the picture) based on those two variables (dates). and after that you can have your limit in R8 or simple "limit 9" in the query formula – user0 May 21 at 19:16
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.