Retrieve last n rows based on one numeric column in google sheet

Question

My data looks like this:

+---------------+-----+-----+------+-----+-----+
| Serial Number | LSL | LCL | DATA | UCL | USL |
+---------------+-----+-----+------+-----+-----+
|             1 |   1 |   3 | 2.3  |   7 |   9 |
|             2 |   1 |   3 | 3.1  |   7 |   9 |
|             3 |   1 |   3 | 2.7  |   7 |   9 |
|             4 |   1 |   3 | 4.9  |   7 |   9 |
|             5 |   1 |   3 | 5    |   7 |   9 |
|             6 |   1 |   3 | 3    |   7 |   9 |
|             7 |   1 |   3 | 10   |   7 |   9 |
|             8 |   1 |   3 | 7.8  |   7 |   9 |
|             9 |   1 |   3 |      |   7 |   9 |
|            10 |   1 |   3 | 6.8  |   7 |   9 |
|            11 |   1 |   3 | 10   |   7 |   9 |
|            12 |   1 |   3 | 3.9  |   7 |   9 |
|            13 |   1 |   3 | 11.3 |   7 |   9 |
|            14 |   1 |   3 |      |   7 |   9 |
|            15 |   1 |   3 |      |   7 |   9 |
|            16 |   1 |   3 |      |   7 |   9 |
|            17 |   1 |   3 |      |   7 |   9 |
|            18 |   1 |   3 |      |   7 |   9 |
|            19 |   1 |   3 |      |   7 |   9 |
|            20 |   1 |   3 |      |   7 |   9 |
+---------------+-----+-----+------+-----+-----+

I want to query last 7 rows data where the DATA column is not empty. Trying to achieve something like this:

+----+---+---+------+---+---+
|  7 | 1 | 3 | 10   | 7 | 9 |
|  8 | 1 | 3 | 7.8  | 7 | 9 |
|  9 | 1 | 3 |      | 7 | 9 |
| 10 | 1 | 3 | 6.8  | 7 | 9 |
| 11 | 1 | 3 | 10   | 7 | 9 |
| 12 | 1 | 3 | 3.9  | 7 | 9 |
| 13 | 1 | 3 | 11.3 | 7 | 9 |
+----+---+---+------+---+---+

But currently, I am only able to get the last 7 rows data which looks like this:

+---------------+-----+-----+------+-----+-----+
| Serial Number | LSL | LCL | DATA | UCL | USL |
+---------------+-----+-----+------+-----+-----+
|            14 |   1 |   3 |      |   7 |   9 |
|            15 |   1 |   3 |      |   7 |   9 |
|            16 |   1 |   3 |      |   7 |   9 |
|            17 |   1 |   3 |      |   7 |   9 |
|            18 |   1 |   3 |      |   7 |   9 |
|            19 |   1 |   3 |      |   7 |   9 |
|            20 |   1 |   3 |      |   7 |   9 |
+---------------+-----+-----+------+-----+-----+

The formula I used is:

=SORT(QUERY(Sheet1!A7:F,"order by A desc limit 7"),1,1)

This formula does not incorporate the condition that the last row of DATA column must not be empty. Is there a way to achieve what I am looking for?

Answer 1

Assuming your serial numbers are consecutive and sorted as such.

=QUERY(A:F,"Select * where A >= "&ARRAYFORMULA(INDEX(SORT(A2:F,1,false),MATCH(true,ISNUMBER(INDEX(SORT(A2:F,1,false),,4)),0),1))-6&" limit 7")

Breakdown:

=QUERY(A:F,"Select * where A >= "

//index used to find the first serial number with a number in the data column
&ARRAYFORMULA(INDEX(

//reverse order
SORT(A2:F,1,false),

//find first number in data column of reversed data
MATCH(true,ISNUMBER(

//get fourth column (data column) to check for numbers
INDEX(SORT(A2:F,1,false),,4)

//minus 6 so you can get the 6 rows above and the row found
),0),1))-6

//get the first 7 rows from the serial number that matches.
&" limit 7")

EDIT

After our conversation:

If your first column is a date and your dates are consecutive with no duplicates, you can use this:

=QUERY(A:F,"Select * where A >= date '"&TEXT(INDEX(SORT(A2:F,1,false),MATCH(true,ISNUMBER(INDEX(SORT(A2:F,1,false),,4)),0),1)-6,"yyyy-mm-dd")&"' limit 7")

Breakdown:

=QUERY(A:F,"Select * where A >= date
//date tells query that it's looking for a date value

'"&TEXT(INDEX(SORT(A2:F,1,false),MATCH(true,ISNUMBER(INDEX(SORT(A2:F,1,false),,4)),0),1)-6,

"yyyy-mm-dd")&"' limit 7"))
//text formats the date in the way that query requires: yyyy-mm-dd