[Solved] Filter blank cells without macro

[FKlusmann]

I have tables of tests in which it is valid for cells not to have value. Many cells may hold the same value.
I wish to 'chart' the last 10 tests which hold values.
I have been trying to use LARGE and VLOOKUP as suggested in the past, now find that they do not provide the expected results.
With filters, I may overcome this, even have a macro to do it. ------ The dreaded macro error message!
I appreciate all suggestions. I may be slow responding due to real life issues.

[Villeroy]

Code: Select all

SELECT * FROM (SELECT * FROM "List" WHERE NOT "Value" IS NULL) ORDER BY "Test#" DESC LIMIT 10

I can't imagine how to do that with an open-ended sheet list.
Define the source list as a database range.
Apply a standard filter with the option to copy the result to another range.
Apply another standard filter on that list with "Test#" <Largest> 10
Next time: Data> Refresh on both ranges.

[FKlusmann]

Thank you, Villeroy.
This seems to rank by value.
I need to keep the values in their sequence, and just use the most recent (not empty) cells.

[Villeroy]

If the Test# column is a sequence of unique numbers in ascending order and you filter by top 10, the resulting row set reflects the 10 bottom rows. If you would use the date column, the top 10 would include the latest 10 rows.

Alternative:
Define the source range (Test#, Value, may be Dates) as a database range.
Connect a Base document to the spreadsheet.
Add a query which selects * FROM "DBRange" WHERE NOT "Value" IS NULL
Drag that query into your spreadsheet.
The resulting database range will be named "Import1" and it will adjust to the size of the query result when updated.

It is not possible to make another query on a query when the source is a spreadsheet.

The following array formula returns the last 10 rows:
=OFFSET(Import1;ROWS(Import1)-10;10;0) [Ctrl+Shift+Enter]

Well, why not a database in the first place?

[Zizi64]

ChartProblem_Zizi64.ods

(21.89 KiB) Downloaded 96 times

[FKlusmann]

Thank you again, Villeroy.

If the Test# column is a sequence of unique numbers in ascending order and you filter by top 10, the resulting row set reflects the 10 bottom rows. If you would use the date column, the top 10 would include the latest 10 rows.

On any given date tests may (or not) be run. The value for a test may be blank. I wish to chart these values in the testing sequence. Sorting by value (or anything), selecting the top 10 may (does) provide erroneous results.

Well, why not a database in the first place?

I have tried to use OooBase and LO's Base and have been frustrated by the crashes and lost time, especially when recovered files are corrupted. Even with "Always create a backup" and "autosave every three minutes". Then you have told me that I should not use base as a development tool.

[FKlusmann]

Zizi64
Thank you, Thank you, Thank you! I believe that you have done it!
Nagyon szépen köszönöm a segítséget.

[FKlusmann]

ChartProblem_Zizi64.ods

Thank you, Thank you, Thank you! I believe that you have done it!
Nagyon szépen köszönöm a segítséget.

[Zizi64]

I believe that you have done it!

It was not too hard with an excellent tool like the LibreOffice (and the AOO)