String Comparision

Hi,

I am having my data from A1:A1000 in which I want to know if there are any repeatitions. The problem is that the order of words may not be same. For example strings "I am fine" and "Fine I am" are to be recognised as repeatitions.

Can anyone out there help me to make my task easy by giving me a macro.

Thank you.

Hi TRJJK73,

Well, without using some interesting VBA, this might get you at least close to what you're after. The array formula shown below will sum the CODE values of all characters in a cell. This will give you a number, that, while not guaranteed to be unique, will definitely allow you to narrow down any potential matches.

With your list in A1:A1000, put this formula into B1 and fill it down to B1000 (remember that since it's an array formula, after typing it you must press CTRL+SHIFT+ENTER, not just ENTER!):

=SUM(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(UPPER(A1)))),1)))

Now if you sort columns A and B by column B, any duplicates in column B *could* be a match of words in a cell. I say could because two cells, while containing different words, might use the same exact letters, numbers and spacing which would create a sum equal to another string using those same characters in different words.

The rat meat = THE RAT MEAT = 815
Tame the rat = TAME THE RAT = 815
Tar the team = TAR THE TEAM = 815

Notice all three cells result in 815 because they use the exact same letters and number of spaces, but their words do not match. This is the distinction you would need to make to identify true matches. I don't foresee you having an abundance of cells that fit this scenario, but it could happen on occasion.

Duplicate sums could also occur with words that don't even share the same letters. For example, the sum of the codes for the words DATE (68,65,84,69=286) is the same as HEAP (72,65,69,80=286). With multiple words per cell, the likeliness of two matches is probably small once again.

Try it and let us know!

This UDF might help.
=CountDisorderedWordMatch("I am Fine",A1:A100) will return how many cells in A1:A100 have the same words as "I am Fine".
The match is case insensitive and word order insensitive.
"I am Fine" matches "fine I am", but matches neither "I am very fine" nor "I am fine."

Please Login or Register  to view this content.