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

[Shelley5683]

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:

Remarks	Column 2	Column 3
same as submitted	12345678911	"null"
123546548	55555564891	123546548
xyz	565765454	"null"

Expected results:

Remarks	Column 2	Column 3
same as submitted	12345678911	12345678911
123546548	55555564891	123546548
xyz	565765454	"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 solution]

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 solution]

@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

[Shelley5683]

Both of these worked as a solution. I chose the filter for now, however with more complex remarks, I may have to change this to the IF statement. I appreciate both of you for the assistance!

:)