Comparing a text, replacing null with a column 2 or leaving null

Question

I have 3 columns that I am comparing or pulling information from. I need to use my remarks column to verify if "same as submitted" is present, if so and Column 3 is null, then replace with Column 2.

Example:

RemarksColumn 2Column 3same as submitted12345678911"null"12354654855555564891123546548xyz565765454"null"

Expected results:

RemarksColumn 2Column 3same as submitted123456789111234567891112354654855555564891123546548xyz565765454"null"

Current formula that I am using to replace the "null" in Column 3 with the value in Column 2, but I am only wanting this to replace the "null" in Column 3 if remarks are "same as submitted". I want the remarks that are "xyz" with a "null" in Column 3 to remain "null".

IF (IsNull([Column 3])) THEN
[Column 2]
ELSE
[Column 3]
ENDIF

Stuck,

Shelley

gc · Accepted Answer

Can you Filter on column Remarks where Remarks = "same as submitted", make your column 3 change on just those records that pass through the T output, then Union those records with your F output from Filter?

NicoleJohnson · Accepted Answer

@gcsolution is definitely a cleaner way to do it, but if you do want to capture everything in one IF formula, you just need to make it a nested IF formula:

IF [Remarks]="same as submitted" THEN (IF IsNull([Column 3]) THEN [Column 2] ELSE [Column 3] ENDIF) ELSE [Column 3] ENDIF

So it will first check to see if Remarks are "same as submitted", and then only check for null  status in Column 3 if that is true, otherwise it will leave Column 3 as is.

Hope that helps! :)

NJ