Matching words from 3 different columns against 1 column

[ben_d_jacob]

Hi everyone, 

I would like to check if the Words (not characters) from column 1, column 2 and column 3 are in column A and it should not match null values.

Can you please suggest a method to do this.

I might not be able to share the screenshot as it is classified data

so, the columns 1,2 & 3 should look for values in column A as words...for example, in column 1 there is a word INT and in the same row in column A the word is INTERNATIONAL...this should not be a match

[ChrisTX]

Can you provide some fictional sample data?  For your 1 column, is there only one word in the column?

[AngelosPachis]

Hi @ben_d_jacob,

What you can do is transpose all columns you look to want into and then use a find & replace tool to look for certain words within those columns.

Then you can append a character ("y" in this occasion when the word you were looking for was found in a particular column

Hope that helps, let me know if that worked for you

Cheers,

Angelos

[afv2688]

Hello @ben_d_jacob ,

you can also achieve this with a filter tool:

Contains([Column A], [Column 1],1) OR
Contains([Column A], [Column 2],1) OR
Contains([Column A], [Column 3],1) 

Regards

[ben_d_jacob]

Hi, The problem with this is,

In column 1, I have words like INT and in column A, I have words like MAINTENANCE.

So, Now it is matching because INT is there in maINTenance.

I do not want this, only if there is a word such as INT in my column A, then it should match.

Can you please help as this is a time sensitive matter

[ben_d_jacob]

removed image

[AngelosPachis]

Hi @ben_d_jacob,

In the workflow I attached above, if you look at the configuration window of the Find & Replace tool, you will see that there is an option that allows you to only Match Whole word Only.

If you check that checkbox, then probably the results will be what you are after

Cheers

Angelos

[ben_d_jacob]

Hi,

I cannot transpose the particular data. I need to work with the data as it is

[ben_d_jacob]

can we use a formula tool, that will check if the words in column A are there in Column 1, column 2 and 3?

[afv2688 · Accepted solution]

Hello @ben_d_jacob ,

How about using an unorthodox method like this filter:

Contains(' '+[Column A]+' ', ' '+[Column 1]+' ',1) OR
Contains(' '+[Column A]+' ', ' '+[Column 2]+' ',1) OR
Contains(' '+[Column A]+' ', ' '+[Column 3]+' ',1)

Not the most pretty one but should do it.

Regards