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

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?

--
Thanks
ETC

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

If you want to see whether the first 9 characters of A1 matches the
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.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525426

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

Very nice, dll,.....very nice.

Vaya con Dios,
Chuck, CABGx3



"daddylonglegs" wrote:


If you want to see whether the first 9 characters of A1 matches the
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.


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525426

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

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


"Sweetetc" wrote:

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?

--
Thanks
ETC

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

Another way



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



--

Regards,

Peo Sjoblom


"Sweetetc" wrote in message
...
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?

--
Thanks
ETC

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

You folks are the greatest thanks
--
Thanks
ETC


"Sweetetc" wrote:

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?

--
Thanks
ETC

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

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


"Sweetetc" wrote:

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.
--
Thanks
ETC


"Ron Coderre" wrote:

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


"Sweetetc" wrote:

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?

--
Thanks
ETC

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

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.
--
Thanks
ETC


"Ron Coderre" wrote:

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


"Sweetetc" wrote:

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?

--
Thanks
ETC

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

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.


--
Thanks
ETC


"Sweetetc" wrote:

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?

--
Thanks
ETC

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

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

Works well when you do the correct things
--
Thanks
ETC


"Ron Coderre" wrote:

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


"Sweetetc" wrote:

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.
--
Thanks
ETC


"Ron Coderre" wrote:

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


"Sweetetc" wrote:

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?

--
Thanks
ETC