Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index


Hello,

There are ways of doing this without scripting, but if your entries
contain text, then scripting may be required, because my method of
sorting/removing duplicates requires the RANK() function and this
function only works with numbers. If your cells do contain text and the
lengths are short (about 5-6 characters), it is possible to convert
these strings into a sort of number that RANK() can process, and then
you can do what you want: remove blanks, remove duplicates, and sort.

However, if you just want to remove the blank entries, you can
iteratively search with the MATCH() function and look for a non-blank
string (something like "[\d\D]{1,}" in LO and "*" in Excel). You can
make the determination of which you are using with the formula
=IF(ISERROR(INDIRECT("Config.A1")),1,0), provided you have a sheet
named "Config": the result will be 1 on Excel, 0 on LO. You can then
use that result to initialize the search string and store it in a named
cell for the iterative search. The formulas would look something like
this (NonBlank is the name of a cell containing the search string):

Cell A2: =2+MATCH(NonBlank,
OFFSET(INDIRECT(ADDRESS(2,xx,,,Sheet)),0,0,1000,1),0)
Cell A3: =A2+MATCH(NonBlank,
OFFSET(INDIRECT(ADDRESS(A2+1,xx,,,Sheet)),0,0,1000,1),0)
Cell A4: =A3+MATCH(NonBlank,
OFFSET(INDIRECT(ADDRESS(A3+1,xx,,,Sheet)),0,0,1000,1),0)
And so on.

In the formulas above, Cell A2 is different as it needs to contain the
first non blank entry. I used "2" in the ADDRESS() function because I
am assuming your first data line is the second one. The "xx" is the
column number of where you are reading from, "Sheet" is the worksheet
that contains the data. At one point, the cell value will generate an
error (MATCH() will fail), and that indicates you have reached the end
of the data list.

I hope this helps.
Rémy.


Le mercredi 18 août 2021 à 13:57 +1200, Martin F Krafft a écrit :
Regarding the following, written by "Brian Barker" on 2021-08-18 at
02:33 Uhr +0100:
Sorry, but I have no idea what this picture represents. It is
usually 
more helpful to explain how your sheets are organs.

Sorry, these were ASCII-formatted tables, but something munged them 
on the way. Here is what the HTML-rendering looks like:

https://i.imgur.com/rHHekvu.png

So I want to "query" these sets of tables and for e.g. "Thu", get 
the values of the non-empty cells in the "Thu" columns on the 
various sheets.

I can do an array copy, but that includes the empty cells.

Also, ideally, I get the output sorted somehow.

-- 
@martinkrafft | https://matrix.to/#/#madduck:madduck.net
  
"if you are going to run a rinky-dink distro made by a couple of
  volunteers, why not run a rinky-dink distro made by a lot of
  volunteers?"
                                                   -- jaldhar h. vyas
  
spamtraps: madduck.bogus@madduck.net



-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy

Context


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.