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.
