Excel 2007 : Score the similarity of the characters between several cells

[glux]

Hi people!!

I'd like to give a "similarity" score to each code from a list of 4-digit codes:

1321
1311
1311
3533
4925
1389
4922

If one of the codes has the same first 4 digits than another code, it will receive a score of 4. If it has the same first 3 digits, a score of 3, and so on until 0.

The result of the above list should resemble to this:

1321 2
1311 4
1311 4
3533 0
4925 3
1389 2
4922 3

Is it possible using an Excel formula?

Your help is very much appreciated!

[glux]

I'm attaching the list so you can practice ;-) target-to-target similarity 3.xlsx

[Marcol]

A bit long winded but try this in B2

Please Login or Register  to view this content.

Drag/Fill Down.

This should also work

Please Login or Register  to view this content.

These will return the values requested in post #1, in your attached sample the expected result differs.

[glux]

Hey Marcol!!! Thanks so much!! It's working!!

Great job!

It's actually the first step of a much more complex problem...

But I will create a new post for the next step... and I will mark this post as SOLVED cause I like to see solved problems in this forum!! ;-)

[glux]

Hello Marcol... if you would like to help with the next step of my problem... here's the thread: http://www.excelforum.com/excel-2007...93#post2701493...

... there's gonna be a 3rd step ;-)

[glux]

Hello Marcol, the 2nd step was solved already!! Now I still have one final step: if you want to help, here's the thread: http://www.excelforum.com/excel-2007...26#post2701526