How to Input text or replace existing text, if the line item fulfills certain conditions.

Question

How input formatted text to certain cell, A. if 1) codes in column "Account" start with"1011"; 2) the codes in column "Offset vendor" start with "E"; 3) the figure in "Amount in local currency" is <0 Input -- "Payment" + "yyyymm" (per column "Posting Date" )+"Offset vendor description" in column "Text" B. if 1) codes in column "Account" start with"1011"; 2) the codes in column "Offset vendor" are 8-digit number 3) the figure in "Amount in local currency" is <0 Input -- "Payment" +"Offset vendor description" +"TBD"in column "Text" C. if 1) codes in column "Account" start with"1011"; 2) the codes in column "Offset vendor" is null or the column is empty; Keep existing data in column "Text". D. if 1) codes in column "Account" start with"20111100"; 2) the codes in column "Offset vendor" start with "E"; 3) the column "Clearing Document" is not empty, and the code in column "Clearing Document" is same with the code in column “Document Number” 4) the figure in "Amount in local currency" is >0 Input -- "Payment" + "yyyymm" (per column "Posting Date" )+"Offset vendor description" in column "Text" E. if 1) codes in column "Account" start with"20111100"; 2) the codes in column "Offset vendor" are 8-digit number 3) the column "Clearing Document" is not empty, and code in column "Clearing Document" is same with the code in column “Document Number” 4) the figure in "Amount in local currency" is >0 Input -- "Payment" +"Offset vendor description" +"TBD" in column "Text" F. if 1) codes in column "Account" start with"20111100"; 2) the codes in column "Offset vendor" are 8-digit number 3) the column "Clearing Document" is not empty, and code in column "Clearing Document" is not same with the code in column “Document Number” 4) the figure in "Amount in local currency" is >0 or <0 Keep existing data in column "Text". JEs- GLs_0322_vendor,payment.xlsx

SPetrie · Answer

Another option is to use the dynamic replace tool and format individual expressions for each of your cases. This may work better if your conditions change and you need to update one or more of the conditions or add additional ones. New FieldBoolExpressionExpressionTextLeft([Account],4)="1011" AND Left([Offset vendor],1)="E" AND [Amount in local currency]<0Payment +DateTimeFormat([Posting Date],"%Y%m")+" "+[Offset vendor description]TextLeft([Account],4)="1011" AND REGEX_Match([Offset vendor],"\d{8}") AND [Amount in local currency]<0Payment +[Offset vendor description]+" TBD"TextLeft([Account],4)="1011" AND IsEmpty([Clearing Document])[Text]TextLeft([Account],8)="20111100" AND Left([Offset vendor],1)="E" AND !IsEmpty([Clearing Document]) AND [Clearing Document]=[Document Number] AND [Amount in local currency]>0Payment +DateTimeFormat([Posting Date],"%Y%m")+" "+[Offset vendor description]TextLeft([Account],8)="20111100" AND REGEX_Match([Offset vendor],"\d{8}") AND !IsEmpty([Clearing Document]) AND [Clearing Document]=[Document Number] AND [Amount in local currency]>0Payment +[Offset vendor description]+" TBD"TextLeft([Account],8)="20111100" AND REGEX_Match([Offset vendor],"\d{8}") AND !IsEmpty([Clearing Document]) AND [Clearing Document] !=[Document Number] AND [Amount in local currency]!=0 [Text] There is also the added benefit of seeing how many times each of those expressions is found to be true in your data set. I added the last row with -1 as the BoolExpression as a catch-all for anything that doesnt match one of the expressions. Without that, any row that doesnt fit one of the above expressions will become a Null.

Christina_H · Answer

This formula handles A, B, D and E, and I've left [Text] in the ELSE clause. Did you want something different to happen to anything that doesn't meet any of the criteria? IF (Left([Account],4)="1011" AND Left([Offset vendor],1)="E" AND [Amount in local currency]<0) OR (Left([Account],8)="20111100" AND Left([Offset vendor],1)="E" AND !IsEmpty([Clearing Document]) AND [Clearing Document]=[Document Number] AND [Amount in local currency]>0) THEN "Payment "+DateTimeFormat([Posting Date],"%Y%m")+" "+[Offset vendor description] ELSEIF (Left([Account],4)="1011" AND REGEX_Match([Offset vendor],"\d{8}") AND [Amount in local currency]<0) OR (Left([Account],8)="20111100" AND REGEX_Match([Offset vendor],"\d{8}") AND !IsEmpty([Clearing Document]) AND [Clearing Document]=[Document Number] AND [Amount in local currency]>0) THEN "Payment "+[Offset vendor description]+" TBD" ELSE [Text] ENDIF