INTRODUCTION:
I have saved the spreadsheet as .ods
example of non duplicate code: N$6 = CUT01 N$5 = “” (enter CUT01 in B116 and 77 in D116)
example of duplicate code: PL$6 = DG09 PL$5 = 40cm
PM$6 = DG09 PM$5 = 35cm
PN$6 = DG09 PN$5 = 30cm (enter DG0930cm in B116, and 30cm in C116, and 77 in D116)
In Microsoft Office the following formula works for non duplicate code and duplicate code:
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
NOTE: the above formula is in the cell ref PN116 for this case
Other formulas like it are in cell refs N116, PL116, PM116
For non duplicate code
CORRECT OUTPUT: N116 = 77
For duplicate code
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77
HOWEVER ++++++++++
In Libre Office the following only works for non duplicate codes
=-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))
CORRECT OUTPUT: N116 = 77
In Libre Office the following only works for duplicate codes
=-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 )))
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77
COMBINING ‘FORMULA THAT WORKS WITH NON DUPLICATE CODE’ WITH ‘FORMULA THAT WORKS WITH DUPLICATE CODE’, USING ‘IF’ COMMAND, CONDITION: N$5>0
In Libre Office the following only works for non duplicate code
=IF(N$5>0,(-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(N$6,$B116,1),FIND(N$5,$C116,1))>0,$D116,0),0 ))))
CORRECT OUTPUT: N116 = 77
=IF(PN$5>0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
For duplicate code the INCORRECT OUTPUT: PL116 = 77 and PM116 = 77 and PN116 = 77, where only PN116 must = 77
In Libre Office the following only works for duplicate code
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
CORRECT OUTPUT: PL116 = 0 PM116 = 0 PN116 = 77
=IF(N$5=0,(-1*(IFERROR(IF(FIND(N$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(N$6,$B116,1),FIND(N$5,$C116,1))>0,$D116,0),0 ))))
For non duplicate code the INCORRECT OUTPUT: N116 = 0, where N116 must = 77
Why?
HOW LIBRE OFFICE SHOULD PARSE:
=IF(PN$5=0,(-1*(IFERROR(IF(FIND(PN$6,$B116,1)>0,$D116,0),0))),(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 ))))
IF PN5 contains a value, in this case 30cm the opening IF command should return a ‘false’ which in this case is the formula:
(-1*(IFERROR(IF( AND(FIND(PN$6,$B116,1),FIND(PN$5,$C116,1))>0,$D116,0),0 )))
This above formula already works correctly by using ‘find’ to compare value in PN6 with what was entered in B116, a match occurs when the value counting from the first digit in B116 matches the value counting from the first digit in PN6 …in this case the value is ‘DG09’
AND
‘find’ compares value in C116 from the first digit with value in PN5, from the first digit. SO if a match is found in PN6 and PN5 as compared to what was entered in B116 and C116 then the value entered in D116 is returned as a ‘true’ statement, in this case 77 in PN116
Any help in getting the long formula to work for both non duplicate codes and duplicate codes in Libre Office will be appreciated. Since it DOES WORK in Microsoft Office, as is.