Compare words between two cells for percentage match

Hi folks,

I have a question that is similar but different to an earlier post (April 26 to be precise - same title). I am a relative beginner with VB so it would be fantastic if someone could lend me a hand.

I have two columns of text each with 1,000+ lines. I want a formula that generates a percentage match for the words in cells of the same row (eg cells B1 and C1).

I have a formula (courtesy of the earlier post) that counts the number of differing characters in the two cells (see below for VBA code). What I really want is a formula that counts the number of differing words.

Example:
Cell A1: the cat sat on the mat
Cell B1: the hat was squashed by the cat

The Levenshtein formula (code and link below) gives me an output of 14 ie, there are 14 different characters in cell B1 than in cell A1. The result I am looking for is that there are 4 words used in cell B1 that are not used in cell A1.

If there is a way to measure the change in the order of words I would be interested in that also, but I realise that is probably very complicated.

Any help is much appreciated!

The formula I have was posted on this forum by SpeedingLunatic in April 2010 and is a Levenshtein measure. The VBA was borrowed from here: http://en.wikibooks.org/wiki/Algorit...htein_distance and is below...

Please Login or Register  to view this content.

You could use this function:

Please Login or Register  to view this content.

Regards,
Antonio

Below is similar but has case-sensitivity.

Please Login or Register  to view this content.

Regards change in order - you might need to specify some examples so we can better interpret requirements.

Note: regards both UDFs presented - both assume a consistent delimiter between words (ie no punctuation per se) - if this is not reality you will need to clean the strings before comparing.

Please Login or Register  to view this content.

if you also want to take the words' order into account :

Please Login or Register  to view this content.

In this last case you can choose to weigh the different matches: a return of 4 consecutive words can be multiplied by 4, a match of 3 consecutive words can be multiplied by 3.
It's also possible to count the different kind of matches in separate variables or array-elements.

You can put the code in the sheet-module in which you want to count the words.
If you put them in another module (macro/workbook/userform module) you'll have to add the sheetname the cells are in.
NB. the preceding period: .Cells(...

Please Login or Register  to view this content.

Please Login or Register  to view this content.