Matching with list based on a value in other column (2 scenarios)

Question

Hello, I'm asking for help again. My task is to validate if data in one field is properly filled according to data in other field. Both fields contain several values, )(usually comma separated, but that's not always the case)

So for example (scenario 1) I have a list of company names in one column, and list of HQ cities in the other:

IDNameCity1Apple, McDonaldApple: Cupertino McDonald: Chicago2Adidas, Lego, MicrosoftAdidas: Herzogenauras, Lego: Billund, Microsoft: Chicago3Apple, Lego, BayerApple: Cupertino Lego: Billund

I have reference list as well:

NameCityAppleCupertinoMicrosoftRedmondLegoBillundMcDonaldChicagoBayerLeverkusenAdidasHerzogenaurach

I want to compare the city data with reference list to find if the data matches(so. i.e. if there is McDonald listed in the "Name column", there should be "Chicago" in the City. In best case scenario, the "Chicago" should follow "McDonald:" string, but I can manage it if It will only check if the value exists in the column. As a result I should get list of Record ID's with errors and information what Company/City combination is missing (In the best case, if there is wrong value, it should be listed as well:

So the output table should look like this:
Minimum variant:

IDName without match2Microsoft3Bayer

Best case:

IDName without matchCity2MicrosoftChicago3Bayer

I did similar job but for data in one column - the solution is to split data into rows, compare with reference list, and then list those which are not matching. But I'm a bit stuck with how to deal with two columns.

Second scenario is similar, but with several reference lists, based on the value in one column. So for example if in the column A the value is "1", I should expect X, Y or Z (or any combination of these) in column B; if column A has value of "2", then in "B" should be something from L, M, or O, etc.
This can be done with cascade filters (i.e. filter out all values containg "1" in column A, then check B with reference list for 1, then from remaing data filter out all records with "2" in column A, and check.... etc.). But if we have wide range of values possible in column A, it will be difficult to manage such workflow. Maybe there is a better way to handle that?

Sample list of values is here:

IDColorCode1OrangeOR1, OR2, OR32BlueBL1, BL2, BL33RedR1, R3, R64GreenG7, G8, G6

Reference lists:

ColorCodesOrangeOR1OrangeOR2OrangeOR3OrangeOR4OrangeOR5

ColorCodesBlueBL1BlueBL2BlueBL3BlueBL4BlueBL5

ColorCodesRedR1RedR2RedR4RedR5RedR7

ColorCodesGreenG5GreenG6GreenG8GreenG9GreenG10

And the desired output:

IDColorWrong code3RedR33RedR64GreenG7

It doesn't matter if wrong codes will be listed in separated rows or combined in one, I just need to know what are the wrong values

AncientPandaman · Accepted Answer

you just need to use multiple criteria for join tool. left/right joiner will be your answer. (depend on the input.)

so the issue is how to split to right data table.

i split the name first than replace with a "+" in front (or any unusual character) than use it split to lines than split to columns.

Christina_H · Accepted Answer

Second scenario is easy enough.  I'm still working on the first!

Christina_H · Accepted Answer

And here's a solution for scenario 1