LibreOffice Calc formula to check if all the numbers with a specific text are found in a column

English
#1

I am using Ubuntu 22.04 and LibreOffice 7.5.7.1. I have a LibreOffice Calc Spreadsheet in which there is a column which contains text such as “INDIAN PENAL CODE–” There may be some other text before “INDIAN PENAL CODE–” and there may be some other text after “INDIAN PENAL CODE-- then a three digit number.” Then may or may not be a comma and another three digit number and so on. In another sheet there is a column in which certain three digit numbers are given.

I want a formula which will check whether there is a text “INDIAN PENAL CODE–” in the cell and if the numbers separated by a comma with “INDIAN PENAL CODE–” are found in the column of a different sheet.

If it is found it would return the value “Compoundable” and if the number with “INDIAN PENAL CODE–” are not found in the column it should return “Non-Compoundable”

My Spreadsheet is attached herewith.
Compoundable Section Checker Query.ods (49.9 KB)

Example:- In “INDIAN PENAL CODE–323,324,448,504,506” 324 is not compoundable and it is not in the list of CompoundableSection. Therefore I want that in front of “INDIAN PENAL CODE–323,324,448,504,506” “Non Compoundable” should be written.

#2

Either I misunderstood your description of the task, or in your example there is not a single line for the “Non-Compoundable” case.

The first thing that comes to mind is something like

=CHOOSE(SUMPRODUCT(NOT(ISERROR(SEARCH('Compoundable Section'.$A$1:$A$54;A3))))+1;"";"Non-Compoundable";"Compoundable")
#4

As obviously @JohnSUN also saw it, I couldn’t find a data row where all the 3-digit-numbers were found in the second sheet.
I therefore read the question as if it was
“LibreOffice Calc formula to check if all the numbers with a specific text some of the numbers occurring in a textual data representation are found in a given column”.

Please clarify.

#7

The example is lacking a sufficiently clear and explicitly assured syntax allowing to identify in what way the list of valid numbers is to be cut out.
Take
sotxINDIAN PENAL CODEottx --498A,294,211,506,508,510,211,34,netx (basically from row 137) as an example.

  • Needs it be analysed due to the part “INDIAN PENAL CODE”?
  • Is the double dash assured to be present, and to start the actual list?
  • Is the list then empty because the first number has an “A” in the position of a fourth digit?
  • Or is 498 the only valid number because the list now is terminated by the “A” (my understanding of what you wrote)?

As long as these questions - and all the others needing to be taken in account- not are clearly and finally answered the strings in column A simply are no data at all.
Based on the given information the only thing I can promise concerning any suggested solution is that there will be errors. That’s not because contributors created erroneous formulas, but because the validity of the data is doubtable, and the task is unclear.

#3

If I understood the task correctly, there are a few remarks in advance of suggesting a solution:

  1. Don’t use spaces and/or special characters in filepaths.
  2. Don’t use spaces and/or special characters in sheetnames or other chosen technical “names”… And don’t start sheetnames with a digit .
  3. Use the sugggested solution as a step of reorganizing your “data” fundamentlly.
    Compound data like you have, which obviously use a (non-) syntax that seems to have been invented on the fly while typing, are error-prone and cause trouble after trouble.

See attached partial solution.
disask98736Suggestion_Compoundable_Section_Checker_Query.ods (82.2 KB)

1 Like
#5

Example:- In “INDIAN PENAL CODE–323,324,448,504,506” 324 is not compoundable and it is not in the list of CompoundableSection. Therefore I want that in front of “INDIAN PENAL CODE–323,324,448,504,506” “Non Compoundable” should be written.

#6

In other words, “if the (number of commas + 1) is not equal to the count of numbers found in the list, it is Non Compoundable”?

#8

(In the solution of Lupp) If the number of commas in column F is greater than number of semicolons in column G then it is “Non Compoundable” If the number of commas in Column F is equal to number of commas in column G it is “Compoundable”

#9

sorry that’s impossible, you try to imlant therefore a infinite loop, a periodical test back and over the basis cell A(n)

maybe the A(n) cell gets a red background colour if TRUE “not comp”

#10

In column G instead of the row number I want the contents of those rows.

#11

just get rid of ROW() in the formula.

see G3 in the attached
exG3.ods (83.4 KB)

1 Like
#12

If I remove the ROW function and press Control+Shift+Enter it is working for that particular cell. What about the entire column.

#13

in fact, there is a Ctrl+drag ! :innocent:
https://forum.openoffice.org/en/forum/viewtopic.php?t=71900

exG*.ods (91.3 KB)

1 Like
#14

Or, easier than fiddling with Ctrl+Mouse down, select the range starting with that one formula cell, i.e. in the Name Box (Shift+Ctrl+T) enter G3:G2215 and then hit Ctrl+D

#15

Presently, If column no. A contains text “INDIAN PENAL CODE–498A, 34” Column No. F gives result as 498. Or if Column no. A contains text INDIAN PENAL CODE–294,506B,109 it is showing only 294,506 in column no. F. I wish if column no. A contains INDIAN PENAL CODE–354A,354D,506 column no. F should show 354A, 354D, 506. Any solution.

#16

a change in lookup approach may go like full list regex (J1), then your additional pattern(s) (J2)
regexJK.ods (147.1 KB)

but seems you’re heading to a rabbit hole; I guess next is Screenshot from 2024-01-23 11-33-34 :innocent:

1 Like
#17

:grinning: My problem is 498 is compoundable but 498A is not compoundable. So I wish 498A or 506B should not appear in the column of compoundable row. Only the exact value in the sheet CompoundableSection should appear in the column CompoundableRow

#18

interesting twist :joy:

let’s see :
regexJK+354ab.ods (147.0 KB)

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.