Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 12 Apr 2020 06:33:54 +0200Criteria to check if cell value is a datehttps://ask.libreoffice.org/en/question/238080/criteria-to-check-if-cell-value-is-a-date/I am using a function such as COUNTIFS which requires a criteria. It seems you can't use e.g. ISNUMBER(A1:A6) as the criteria in order to check if a value is a date as opposed to text. Is the best approach to enable regular expressions for Calc and to use a criteria such as `"^[:digit:]{2}/[:digit:]{2}/[:digit:]{2}"` for date in the format of "MM/DD/YY", despite caveats such as the need to escape certain characters for all formulas and the fact that this seems to be a program-specific setting, potentially messing with documents from other users that use `*` as wildcards (the default setting)?
If it's the case that most experienced users would probably want regular expressions enabled, then it's not as big of a deal to make the switch to enable regular expressions and learn to deal with it.
I can use SUMPRODUCT as an alternative to COUNTIFS where I can specify the "criteria" using e.g. ISNUMBER(A1:A6), but for some reason it causes a performance issue for my spreadsheet.zfSun, 12 Apr 2020 06:33:54 +0200https://ask.libreoffice.org/en/question/238080/SUMIF with different Criteria values?https://ask.libreoffice.org/en/question/237595/sumif-with-different-criteria-values/I'm attempting to use the SUMIF function to sum the total of numbers in column B based on the values in column A. The values in column A could be either numbers or letters. If a cell is not blank in column A I'm looking for the corrosponding number in column B to be summed.
Tried using wildcards as the Criteria in the SUMIF formula but to no avail. Also tried playing with ISBLANK=0, couldnt get that to work either.
Any thoughts?sdritcheyThu, 09 Apr 2020 00:30:24 +0200https://ask.libreoffice.org/en/question/237595/How do I replace a value that appears in multiple rows if one of the rows contains a certain value in a different column?https://ask.libreoffice.org/en/question/178168/how-do-i-replace-a-value-that-appears-in-multiple-rows-if-one-of-the-rows-contains-a-certain-value-in-a-different-column/Hi all, I've looked everywhere I know to and can't figure out how to do this. I'm also not entirely sure how to ask.
I am trying to search for a value in one cell and replace another cell based on that value. For example, I want to search in B2 and replace A2 if I find a certain value in B2. But I also want it to replace *anything in column A that matches A2*.
Below is an example of original data (on the left) and what I want it to end up as (on the right). In this example, it is searching column B for the characters "XYZ." It is then replacing the value in A2 with "NUM," since it found "XYZ" in B2, and replacing A1 with "NUM" as well since A1 matches A2. It's doing this for the others with "XYZ" as well. I did this manually for the example, but I want to do it with a formula, since I'm working in a sheet with nearly 100000 rows.
![image description](/upfiles/1546539631329536.png)
I know how to search for B2 and replace A2 based on that, but not how to have it replace both A2 and any matches to A2.
Is there a way to do this?lgreeneThu, 03 Jan 2019 19:30:50 +0100https://ask.libreoffice.org/en/question/178168/Conditional format -> formula is -> multiple arguments?https://ask.libreoffice.org/en/question/175046/conditional-format-formula-is-multiple-arguments/ Hi,
I want to apply conditional formatting to a cell with "Formula is", but use two arguments.
I.e. I want to format the target cell, A1, if B1 is empty (for this I use ISBLANK(B1)) and if A2 contains either the text "Green" or "Red".
I've tried various ways to combine arguments (`&`, `&OR&`, `OR`, `;` etc.), but couldn't make it work.
Example: `ISBLANK(B1)&A2="Green"OR"Red"`
Many thanks!patrick.deWed, 05 Dec 2018 01:25:43 +0100https://ask.libreoffice.org/en/question/175046/How to fix DSUM imported from Google Sheets?https://ask.libreoffice.org/en/question/170977/how-to-fix-dsum-imported-from-google-sheets/I'm trying to import a spreadsheet keeping track of my personal expenses from Google Sheets, but DSUM isn't working and I haven't been able to find much online regarding this. What I'm trying to do is, for example, to sum up the expenses ("Amount") in the "Category" of "Food" during the month of January: =DSUM($'2018 - Expenses'.$A:$E,"Amount",{{"Date";"2018-01*"}, {"Category";"Food"}})
This is giving me Error:533, which I haven't been able to find in the documentation. Is this not the correct way to use "Search criteria"? If so, how can something like this be accomplished in LibreOffice?comingfromdocsSat, 03 Nov 2018 18:10:31 +0100https://ask.libreoffice.org/en/question/170977/Formula to find a row that uniquely matches multiple criteria, including a regular expressionhttps://ask.libreoffice.org/en/question/166273/formula-to-find-a-row-that-uniquely-matches-multiple-criteria-including-a-regular-expression/[C:\fakepath\MatchCriteria.ods](/upfiles/15374671682812127.ods)
The following formula counts the number of rows that match certain criteria. **Is there a formula that will find the row that matches those same criteria when there is exactly one such row?**
=IF(LEN(A2),
COUNTIFS(
$Sheet2.$A:$Sheet2.$A, "=" & A2,
$Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
$Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5,
$Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
)
,"")
----------
I'd also like to be able to replace references to A2 in the formula with a regular expression string, which I can do in the formula above because COUNTIFS, COUNTIF and SUMIF all accept regular expressions.
The rest of this post is context for the above question
----------------------------------------
I have two spreadsheets (one sheet in each) both with data relating to the same list of people. The data relating to each person can be used to find which person matches which between the two spreadsheets. Only of the spreadsheets has an ID for each person. I need to add the each person's ID to the spreadsheet that doesn't have them.
To do this I've written a macro that
1) Imports both spreadsheets into a new spreadsheet. The first sheet (Sheet1) in the new spreadsheet is the one without IDs, the second sheet (Sheet2) has the IDs
2) Adds a column to Sheet1 ("Match Count") with a formula for each person which counts the number of rows in Sheet2 that the matches the person's name and other criteria (See below)
3) Adds a column to Sheet1 ("ID") with a formula that shows the ID from Sheet2 when the "Match Count" value is 1.
In Sheet1 there are one or more rows for each person, grouped together. Sheet1 has an "Amounts" field which has a numeric value in each row.
Sheet2 has one row for each person, and has the columns "Number of Amounts", which corresponds to the number of rows for the corresponding person in Sheet1, and "Total Amount" which corresponds to the sum of the "Amounts" values for the same person in Sheet1. For the purposes of matching people between the two sheets "Total Amount" can be up to 0.5 more or less than the sum of the corresponding "Amount" values in Sheet1.
There can be different people with the identical names but they will have different "Amounts".
This is the "Match Count" formula (without the nested SUBSTITUTEs for readability)
A is "Name" in both sheets
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF(LEN(A2),
COUNTIFS(
$Sheet2.$A:$Sheet2.$A, "=" & A2,
$Sheet2.$C:$Sheet2.$C, "=" & COUNTIF($A:$A,"=" & A2),
$Sheet2.$D:$Sheet2.$D, ">" & SUMIF($A:$A,"=" & A2,$B:$B) - 0.5,
$Sheet2.$D:$Sheet2.$D, "<" & SUMIF($A:$A,"=" & A2,$B:$B) + 0.5
)
,"")
That works great, but I want is a formula that gets data from the row that uniquely matches the same criteria when the "Match Count" is 1. **It seems to me that if I can determine that one row uniquely matches certain criteria then I should be able to find that row with a similar formula, but I don't see it. Maybe I'm missing something very simple.**
The first formula that I came up with works in most cases, but does not work when there are more than one rows in Sheet2 with the same name as the person being matched from Sheet1, unless the first row with the matching name in the Sheet2 happens to match all of the criteria.
A is "Name" in both sheets
Sheet1: E = "Match Count" (above)
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF(OR(ISBLANK(A2), E2<>1), "", IF(AND(
COUNTIF($A:$A,"="&A2) = INDEX($Sheet2.$C:$Sheet2.$C,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0)),
SUMIF($A:$A,"="&A2, $B:$B) - 0.5 < INDEX($Sheet2.$D:$Sheet2.$D,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0)),
SUMIF($A:$A,"="&A2, $B:$B) + 0.5 > INDEX($Sheet2.$D:$Sheet2.$D,MATCH(A2, $Sheet2.$A:$Sheet2.$A, 0))
),
INDEX($Sheet2.$B:$Sheet2.$B,MATCH(A2,$Sheet2.$A:$Sheet2.$A, 0)),"Failed") )
The other formula I came up with works but is VERY slow and uses the "MATCH(1, (...) * (...) * (...), 0)" version of the MATCH function which does not seem to allow regular expressions...
A is "Name" in both sheets
Sheet1: E = "Match Count" (above)
Sheet2: B = "ID", C = "Number of Amounts", D = "Total Amount"
=IF( OR(ISBLANK(A2), E2<>1), "", IFERROR(
INDEX($Sheet2.$B:$Sheet2.$B,
MATCH(1,
($Sheet2.$A:$Sheet2.$A=A2) *
($Sheet2.$C:$Sheet2.$C=COUNTIF($A:$A,"="&A2)) *
($Sheet2.$D:$Sheet2.$D < SUMIF($A:$A,"="&A2, $B:$B) + 0.5) *
($Sheet2.$D:$Sheet2.$D > SUMIF($A:$A,"="&A2, $B:$B) - 0.5)
, 0)
)
, "Failed"))
To get around the speed issue by using the Fast formula for all persons that have a "Match Count" of 1, and for any that the formula doesn't work for I can then use the Slow formula instead.
However I also need to be able to use regular expressions. Some of the rows with a "Match Count" of 0 (less than 1%) come about because of minor differences between the way a name is represented, like several spaces in the name in one sheet where there is only one space in the other, or a comma in one that does not appear in the other (e.g. "John Smith, Jnr"). I can handle those variations relatively easily by using a regular expression based on the literal name string, e.g, "John[, ]+Smith[, ]+Jnr". I can't to do that when the formula I'm using doesn't take take regular expressions. I have to use macro code to find the row with all the matching criteria. That works, but I feel that a formula similar to the "Match Count" one would be more efficient.
So - how do I find row that uniquely matches multiple criteria, including a regular expression?
[C:\fakepath\MatchCriteria.ods](/upfiles/15374671682812127.ods)MossyThu, 20 Sep 2018 20:31:16 +0200https://ask.libreoffice.org/en/question/166273/In Calc How do I save sort criteriahttps://ask.libreoffice.org/en/question/148615/in-calc-how-do-i-save-sort-criteria/ I have a complex, 9-criteria, sort in a spread sheet I use daily. When I save and close it, only the first three criteria are saved. I want it to save all 9 criteria.jwbruceThu, 08 Mar 2018 18:18:41 +0100https://ask.libreoffice.org/en/question/148615/