Re: [libreoffice-users] Consolidating values from multiple sheets
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.bo...@madduck.net > -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Consolidating values from multiple sheets
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.bo...@madduck.net -- To unsubscribe e-mail to: users+unsubscr...@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
Re: [libreoffice-users] Consolidating values from multiple sheets
At 22:29 17/08/2021 +1200, Martin F Krafft wrote: I've got several sheets with values such as the following: | Thu | Fri | Sat | |:|:|:| | 5 | 6 | | | | 9 | 10 | | 1 | | 2 | | | 9 | 10 | | 11 | | 12 | | 7 | 8 | | | | 3 | 4 | | 11 | | 12 | Here is another | Thu | Fri | Sat | |:|:|:| | g | h | | | a | | b | | | i | j | | g | h | | | e | f | | | | c | d | | k | | l | Sorry, but I have no idea what this picture represents. It is usually more helpful to explain how your sheets are organs. How would one go about creating a new sheet that consolidates all these together, so that I'd get a list of all the values for each of the columns, i.e. an array with the values {5,1,11,7,11,g,a,g,e,k} for "Thu", and so on, ... Er, exactly how do those become the values for Thursday? o Enter "=" (no quotes) into the target cell in your new sheet. o Click the tab of your source sheet to display it. o Click the source cell. o Press Enter (or click the Accept green tick icon in the Input Line). You can now drag the cell's fill handle in the target sheet to expand the area referenced to a row, a column, or another range. I trust this helps. Brian Barker -- To unsubscribe e-mail to: users+unsubscr...@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
[libreoffice-users] Consolidating values from multiple sheets
Hey, I've got several sheets with values such as the following: | Thu | Fri | Sat | |:|:|:| | 5 | 6 | | | | 9 | 10 | | 1 | | 2 | | | 9 | 10 | | 11 | | 12 | | 7 | 8 | | | | 3 | 4 | | 11 | | 12 | Here is another | Thu | Fri | Sat | |:|:|:| | g | h | | | a | | b | | | i | j | | g | h | | | e | f | | | | c | d | | k | | l | How would one go about creating a new sheet that consolidates all these together, so that I'd get a list of all the values for each of the columns, i.e. an array with the values {5,1,11,7,11,g,a,g,e,k} for "Thu", and so on, and ideally without the blank lines? Is this possible without scripting? It has to run on Excel as well as Calc, and I don't think scripting is a sensible way to go for that. Ideally, I can also suppress duplicates *and* sort the result. Any ideas? -- @martinkrafft | https://matrix.to/#/#madduck:madduck.net "i can stand brute force, but brute reason is quite unbearable. there is something unfair about its use. it is hitting below the intellect." -- oscar wilde spamtraps: madduck.bo...@madduck.net -- To unsubscribe e-mail to: users+unsubscr...@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