Word match between two fields

[Amos1980]

Hi,

I have two files to match between. In one file I have a column which contains a description of a product and the same structure in the other. I want to test how many words match between the two, for example:

Description 1	Description 2	words match
SENSOR DISP.F.OXYMETER NELLCOR904-N	SENSOR DISP.F.OXYMETER	2
1320011 - HEPA FILTER	1320011- HEPA FILTER ESC12001	3
CATH. ENDOTR. BLUE LINE CUFFED טובוס 8	CATH. ENDOTR.	2

Thanks,

Amos

[T_Willins]

Hi @Amos1980,

It takes some data cleansing and may not exactly match your needs, but this workflow gives the result you wanted.  You may need to tweak the data cleanse based on your actual data, but this should give you a start.

[Qiu]

@Amos1980 
Basically same with @T_Willins approach, and I am thinking a library of delimeter might be easy to use.
So if there is some thing new, just update the red line part.

[Amos1980]

Thanks,

The thing is that I couldn't use the record ID since the two fields I'm comparing are in different files and are located in different rows.

What can I do then?

Thanks,

Amos

[T_Willins]

HI @Amos1980 

Is the data formatted in the files so that the first record is compared to the first record, the second to the second, and so on?  If yes, you can join the two files using a Join tool and Join by Record Position.  If not, how do you know which rows should be compared to the rows in the other file?

[Amos1980]

Hi,

The files are very different in size and in rows position.

I tried to use Fuzzy but this is not really the purpose, I just need to know how many words match between rows in both files (could give more than one result per Item).

Currently I'm using a mix of Alteryx and SQL queries but I really want to solve it through Alteryx only.

Thanks

[T_Willins]

Hi @Amos1980,

If I am understanding you correctly, you need to compare every row from one file to every row in the other file.  While this could be done using an Append Fields tool, I am thinking a batch macro may work better.  This batch macro compares every row from Description 2 and compares it to each row in Description 1.  The workflow then summarizes the word count and puts the two descriptions side by side.

Batch Macro

Workflow v2

[Amos1980]

Thanks for the effort,

I'm not sure why, but I'm having trouble downloading the macro.

However I used your first solution with a combination of Fuzzy match from 1% match and that pretty much solved it so thank you very much, it was very helpfull.

Thanks,

Amos

[taylorbyers]

Is there a way to use this to output a list of words that were matches?