Match set of strings to list of strings in one column and return unique values

[lady_alina]

Hi,

Thanks in advance. This site has always been so helpful and I expect to get an answer for this problem as well.

I have given below table to show you example, what I want is set of strings in column B (sheet 1) should match its words with sheet 2 column a and sheet 3 column a if any word is found in sheet 2 column a which is also found in sheet 3 column a then that word should be excluded from the out put like for examples you can check the first column of sheet 1 column C - ideally it should have copied alina, tim but since alina is found in sheet 3 column a it is excluded from the output and only tim is copied. Hop eI made myself clear. Please help me with a vba code to simplify my work.

Sheet 1
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC THY Alina tim tomorrow[/TD]
[TD]tim[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]WERT 00 PETER TIM[/TD]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GYT GEORGE ALINA GNEH[/TD]
[TD]George, Alina[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]PWO0 MIKE KLERW[/TD]
[TD]Mike[/TD]
[/TR]
</tbody>[/TABLE]


Sheet2
[TABLE="class: cms_table, width: 100"]
<tbody>[TR]
[TD]Alina[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[/TR]
[TR]
[TD]George[/TD]
[/TR]
[TR]
[TD]Alina[/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[/TR]
</tbody>[/TABLE]


Sheet3
[TABLE="class: cms_table, width: 100"]
<tbody>[TR]
[TD]Column A[/TD]
[/TR]
[TR]
[TD]theg[/TD]
[/TR]
[TR]
[TD]uhwt[/TD]
[/TR]
[TR]
[TD]teh alina wer[/TD]
[/TR]
</tbody>[/TABLE]

 

[oldbrewer]

if your string is "abc tim def" and sheet 2 is "abc def tim" and sheet 3 is " abcd time cdef"

how do we identify that tim is a name and abc is not?

 

[lady_alina]

The words will always be separated by space ofcourse.

 

[oldbrewer]

I repeat my question - how do I know "tim" is a name and "abc" is not?

 

[lady_alina]

Forgive me if I am not able to make myself clear. Here it is again what I want

Its the words that we are searching for in sheet 2 column A. - example cell 2 - Tim, cell 3 - Peter, cell 3 - Alina. We are searching this words in sheet 1 column B - Example cell 2 - The tim poe peter, cell 3 theg alina ger00, cell 4 - kle alina peter thew and sheet 3 which has similar kind of column like column b in sheet 1. If any of the words from column A sheet 3 is found in sheet 2 column A then it should be excluded from the output result and which ever word from sheet 2 column A is found in cell 2 in sheet 1 column B then it should be copied, however the result/output should be unique and if found more than one result then it should be like this peter, alina (comma separated results)

 

[oldbrewer]

I CAN search for tim and alina, and reject alina because it is founf in sheet 2 and in sheet 3

BUT

how do I know in the first place I am searching for tim, why is tim different from abc, do you have a list of names somewhere?

 

[lady_alina]

Yes its in sheet 2 column A

 

[Aladin Akyurek]

@lady_alina

1) Is it true that you have duplicates in Sheet2 like Alina?

2) What is the result in C1 of Sheet1 is Tim, that is, how does that obtain?

3) Why no Tim in C2 of Sheet2 along with Peter?

4) How does the list of Sheet3 relate to other sheets?

 

[oldbrewer]

Aladin's questions reflect my thinking. We have not yet defined clearly what you want.

 

[oldbrewer]

[TABLE="width: 696"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]alina[/TD]
[TD]tim[/TD]
[TD]peter[/TD]
[TD]george[/TD]
[TD]mike[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]ABC THY Alina tim tomorrow[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]WERT 00 PETER TIM[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]GYT GEORGE ALINA GNEH[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]PWO0 MIKE KLERW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]theg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]uhwt[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]the[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]alina[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]wer[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]is mike excluded as he is in verification list?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]