[PROBLEM] related to: data cleansing through use of formulas

[Rockodecto]

Hi all,

I've been playing around with formula functions but can't seem to find a solution, so here's my problem as best as I can describe it:

I have 4 columns, let's call them [Column 1], [Column 2], [Column 3], [Column 4]  

[Column 1] contains a list of numbers in ascending order; some numbers are repeated multiple times (random)

E.g. 1, 1, 1, 2, 2, 3, 3, 3, 3, 4, 5, 5...etc

[Column 2] is a list of cases corresponding to the items in [Column 1]; some numbers are not assigned cases, and some cases are repeated

E.g. ABC, [blank], DEF, DEF, XYZ, [blank], etc. 

[Column 3] is a concatenation of [Column 1] and [Column 2]; some combinations are valid and some are invalid

E.g. 1ABC , 1, 1DEF, 2DEF, 2DEF, 3XYZ, 3, etc.

[Column 4] is similar to [Column 3], but it is based off of a different [Column 1] and [Column 2] (not shown); [Column 4] combinations are all errors

E.g. 1XYZ, 1DEF, 2XYZ, 4ABC, etc. (again, these are all invalid combinations)

For each of [Column 1], [Column 2], and [Column 3], there are ~3,000 values. For column 4, there are only ~200 values.

My desired solution is:

If the value in [Column 3] is listed in [Column 4], delete the value in [Column 2]. So, for all [1ABC]s that are invalid, I need it to become just [1] (by deleting out the case code in [Column 2]).

I have attached a file with sample data.

Any help is much appreciated -- thank you in advance! Sorry if this is unclear...I've been trying to use [contain] and [in] formulas with [if] statements, but can't seem to make it work. 

[geraldo · Accepted solution]

@Rockodecto 

I set up a flow for you to have an idea.

The FlagKill column identifies the proposed situation.
Now you need to delete column 2;
FYou said that 1 item must remain. I believe that you can continue;

[Rockodecto]

Thank you so much!!