Finding duplicates in addresses in two columns in the same row, with slight difference like St. and Street

[etbrown]

I saw where someone suggested to just compare the first 11 characters and something like the following formula when comparing c1 and e1
=LEFT(C1,11)
=COUNTIF(_:_,_ _)

I expect I'd put the first formula in an open cell in the first row, like O1, but not sure where to place the COUNTIF

Possibly there's a better solution too.

 

[jasonb75]

Comparing 11 characters is not an exact science as the number of characters in the street name can vary.

Where E1 contains 'Street" and C1 contains "St." this will return TRUE where the rest of the address matches.

=SUBSTITUTE(E1,"Street","St.")=C1

Does that help?

Note that there is not a 1 formula fits all solution for such things. You would need addtional formula tests for each pair of items to compare such as 'Road' and 'Rd.' etc.

 

[etbrown]

The concept of just comparing the first 11 or so characters works for me, as it will catch the vast majority of dupes. It's not about comparing st VS street or av VS Ave, etc. It's about finding a way to identify the vast majority of all those abbreviations.

I know where to position the =LEFT...o1. So where do I place the =COUNTIF. ? ... And what would the rest of the formula be after, =COUNTIF

Again, comparing c1 and e1.

 

[jasonb75]

Whoever told you that you need countif is mistaken. If comparing the first 11 characters is good enough for what you need then

=IF(LEFT(C1,11)=LEFT(E1,11),"Same","Different")

will be adequate.

 

[DanteAmor]

You could give several examples of what you have and what you expect from the result in each example.

 

[etbrown]

Jasons solution worked perfectly!