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

S

Sweetetc

I have two columns of data. Each row cell is 27 characters long. I want to
find any duplicate matches between the two columns on just the 1st 9
characters of each cell. Is there a function that can do this?
 
D

daddylonglegs

If you want to see whether the first 9 characters of A1 matches th
first 9 characters of any entry in B1:B100 then in C1

=ISNUMBER(MATCH(LEFT(A1,9)&"*",B$1:B$100,0))

copy down to check A2, A3 etc
 
R

Ron Coderre

Try something like this:

For lists in A1:B50

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

copy that formula down as far as you need.

If you don't want "hits" on blank cells, use this:
D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")>0


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
R

Ron Coderre

I must be missing something!?!

Using your posted data, I tried both of the formulas I posted AND Peo's
formula.
Ron #1 in D1: =COUNTIF($B$1:$B$50,LEFT(A1,9)&"*")>0
Ron #2 in D1: =COUNTIF($B$1:$B$50,LEFT(A1&" ",9)&"*")>0
Peo #1 in D1: =COUNTIF(B$1:B$100,LEFT(A1,9)&"*")>0

All three formulas, when copied down, returned TRUE, FALSE, FALSE, TRUE,
TRUE, FALSE

Perhaps if you post the exact formula you're using we might spot a difference.

***********
Regards,
Ron

XL2002, WinXP-Pro
 
S

Sweetetc

Spoke too soon they do not seem to compare the way I need.

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

These two columns should return True, False, False, True. True. False,
I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
9 digits in column B Then A2 first 9 digits, etc.
 
S

Sweetetc

spoke too soon this does not seem to comapre what I need

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

These two columns should return True, False, False, True. True. False,
I am aking if for the 1st 9 digits and A1 are there any matches for the 1st
9 digits in column B Then A2 first 9 digits, etc.
 
S

Sweetetc

Thanks for your response Ron DAH!!!! I was not it the absolute cell of B1

Works well when you do the correct things
 
Top