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