Matching of data from different files

[arieta]

Hello,

I have two files in my workflow, and I want to match one column from file 1 with a column from file 2. So, when column from file 1 displays the same text as column from file 2, the data from file 1 match together. However, when column from file 1 displays slightly different text from column in file 2, the data should not match and the output is blank cells. Please see below an example:

Column from file 1:

Column file 1

Banana apple all oranges

Banana two apples oranges

Banana one apple one orange

Banana kiwi all apples

Banana apple all oranges two

Column from file 2

Column file 2	Data that need to follow
Banana apple all oranges	0.05
Banana one apple one orange	0.1
Banana two apples oranges	2
Banana apple melon	0.3

Output as I need it to be (combined and matched):

Column from file 1	Column from file 2	Data
Banana apple all oranges	Banana apple all oranges	0.05
Banana two apples oranges	Banana two apples oranges	2
Banana kiwi all apples	[blank]	[blank]
Banana one apple one orange	Banana one apple one orange	0.1
Banana apple all oranges two	[blank]	[blank]

However, please note that the one from the column in file 2 that do not match with text from the column in file 1 (e.g. Banana apple melon) I want them in a separate output.

Could you please help me with this? Thank you!

[AbhilashR · Accepted solution]

Hi @arieta, does the attached solution get you what you are looking for?

I joined the two datasets using the Join tool on the common keys and used a Union tool downstream to bring multiple outputs of the Join back together. Let us know if this is not what you were looking for.

[arieta]

Thank you very much - it works!