Remove rows when a value in one field is not found within another (nulls may occur):

Question

I have a large report (30x70K) where I need to perform the following action:

If the value in "Source if Appl." is not null, and the value of the cell is not found in "Proj", the field should be removed.  How do I make this occur?  All nulls should remain, but if "Source if appl." does not exist within "Proj" at least once, the entire row should be removed.

An example is found below, with column 4 being an example of the logic of whether the field remains or is removed.

IDProj.Source if appl.*Output intent1ABC

789REMOVE (source if appl. not found in Proj)2123 Keep (no source if appl.)3DEF123Keep (source if appl found in proj.)4GHI Keep (no source if appl.)3JKL789REMOVE (source if appl. not found in Proj)5MNO Keep (no source if appl.)

JoshuaElphee · Answer

The issue is that this is flagging 1 where there ARE comparable values in "proj."   row 3 should remain as the value is found in proj.

Qiu · Answer

@JoshuaElphee 
To reduce the computation, I first filter out the empty "Source if appl." then have to do an Append to locate relation between "the Proj."  and "Source if appl."

0227-JoshuaElphee.yxmd

binu_acs · Answer

@JoshuaElphee Create a variable called flag and assign 1 or 0 based on your condition then filter out the one that you don't want