Compare the 1st 9 digits in two columns looking for duplicates

I had to compare 1st 9 digits in two columns of data to find duplicates within data , i found this thread,

" http://www.excelforum.com/excel-form...uplicates.html "

i used the same data used in the above thread to get similar answer

data :
A B
22345678922223200 12345678922223200
32345678922223200 12345678922223200
42345678922223200 22345678922223200
12345678922223200 12345678922223200
12345678922223200 12345678922223200
24345678922223200 12345678922223200

using formulas
=COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")>0
=COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")>0
=COUNTIF(B$1:B$100,LEFT(A1,9)&"*")>0

all the three aren't working for me (i'm using office 2013)

help me!!


22345678922223200 12345678922223200 =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")>0
32345678922223200 12345678922223200 =COUNTIF($B$1:$B$50,LEFT(A2,9)&"*")>0
42345678922223200 22345678922223200 =COUNTIF($B$1:$B$50,LEFT(A3,9)&"*")>0
12345678922223200 12345678922223200 =COUNTIF($B$1:$B$50,LEFT(A4,9)&"*")>0
12345678922223200 12345678922223200 =COUNTIF($B$1:$B$50,LEFT(A5,9)&"*")>0
24345678922223200 12345678922223200 =COUNTIF($B$1:$B$50,LEFT(A6,9)&"*")>0

returns

22345678922223200 12345678922223200 FALSE
32345678922223200 12345678922223200 FALSE
42345678922223200 22345678922223200 FALSE
12345678922223200 12345678922223200 FALSE
12345678922223200 12345678922223200 FALSE
24345678922223200 12345678922223200 FALSE

it supposed to return

TRUE
FALSE
FALSE
TRUE
TRUE
FALSE

Screenshot (46).png

Screenshot (45).png

oww,, works,, thank you so much BadlySpelledBuoy

it works thank you for quick reply

Happy to help. Thanks for the rep point.

Don't forget to mark the thread as SOLVED if you're happy you have a working solution.

BSB

=IF(SUMPRODUCT(--(LEFT(B1:B50,9)=LEFT(A1,9)))=0,FALSE,TRUE)

This one suits better thanks again