Need Formula to compare two rows

[Mebs]

Need some help here guys !

Is there are way that I can compare three cells and see how close to being the same they are from a list of 2800 rows?

what I do at the moment is;
1) Use Substitute on B1, to remove all spaces and . and - etc
2) Sort the list on column C so that its strict A to Z

Column B Column C
<TABLE style="WIDTH: 483pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=644 border=0><COLGROUP><COL style="WIDTH: 221pt; mso-width-source: userset; mso-width-alt: 10788" width=295><COL style="WIDTH: 214pt; mso-width-source: userset; mso-width-alt: 10422" width=285><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 221pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=295 height=20>A.Stevenson</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 214pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=285>AStevenson</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right width=64>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abbeyfield (Ilkley) Society Ltd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AbbeyfieldIlkleySocietyLtd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>26</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Abc Schools Ltd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AbcSchoolsLtd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>13</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Aberdeen City Council</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AberdeenCityCouncil</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>19</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Aspire Defence Capital Works</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AspireDefenceCapitalWorks</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>25</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Aspire Defence Capital Works(Pcal)</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AspireDefenceCapitalWorksPcal</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>29</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Aspire Defence Ltd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AspireDefenceLtd</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>16</TD></TR></TBODY></TABLE>
What I am trying to achieve is to look through the list and find; for example AspireDefenceCapitalWorks has 25 Characters and the row below it has AspireDefenceCapitalWorks(Pcal) 31 characters but the first 25 are the same so (25 / 29). So there is a 86% chance that they are the same. Or any other way to find out that the cell above and below is probably the same. PS The column with the number in it is the len(column C). Thanks

 

[3link]

Nvm

I don't understand what you mean by three cells. It looks like you're comparing only two on each row. I'm so confused.

Also, on this list of yours, is it possible that the first wo columns won't sync up? What if there is an addition to the list? That would throw everything off, right?

 

[Mebs]

What is Nvm?

 

[3link]

Need clarity / details. See my edit.

 

[Mebs]

@3link

Yes your right its only two cells. Cell one plus the one below it. On the original workbook there will be no further columns but the number of rows will vary. With regards to sync, I don't think that will be a problem too as I manually create column C. Hope this helps.

 

[3link]

I'd do it like this (assuming the first column is in A1 and the second is in B1):

=Small(Len(A1),1)/Large(Len(B1),1)

 

[Mebs]

@3link

Thanks for trying but I'm not sure that this is what i'm after. I'm looking to compare row1 with row2 and see if they are the same or not. If not then how close are they. So row 1 = abcd, row2 = abcd, row3 = abcdef. Now compare row 1 and 2 = 100% match but row 2 and 3 will = 0%. But I know that the first 4 charecters are the same. So based on the comparing 4 charecters then there is a 66% match but not 100%.

 

[3link]

@3link

Thanks for trying but I'm not sure that this is what i'm after. I'm looking to compare row1 with row2 and see if they are the same or not. If not then how close are they. So row 1 = abcd, row2 = abcd, row3 = abcdef. Now compare row 1 and 2 = 100% match but row 2 and 3 will = 0%. But I know that the first 4 charecters are the same. So based on the comparing 4 charecters then there is a 66% match but not 100%.

I see. I'm not sure excel is capable of that kind of comparison. At best, I think you can only determine when they are or are not 100%. I can think of another way using a really complicated 'Left/Right' formula, but that would take so long it wouldn't be worth the time for you to make it.

 

[Mebs]

Thanks for trying anyway.