Thread: Score the similarity of the characters between several cells

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!

A bit long winded but try this in B2

=MAX(IF(COUNTIF($A$2:$A$8,A2)-1>0,4,0),IF(SUMPRODUCT(--(LEFT($A2,3)=LEFT($A$2:$A$8,3)))-1>0,3,0),IF(SUMPRODUCT(--(LEFT($A2,2)=LEFT($A$2:$A$8,2)))-1>0,2,0),IF(SUMPRODUCT(--(LEFT($A2,1)=LEFT($A$2:$A$8,1)))-1>0,1,0))

Drag/Fill Down.

This should also work

=MAX(IF(COUNTIF($A$2:$A$8,A2)-1>0,4,0),IF(SUMPRODUCT(--(INT($A2/10)=INT($A$2:$A$8/10)))-1>0,3,0),IF(SUMPRODUCT(--(INT($A2/100)=INT($A$2:$A$8/100)))-1>0,2,0),IF(SUMPRODUCT(--(INT($A2/1000)=INT($A$2:$A$8/1000)))-1>0,1,0))

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