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

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.

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
>
>

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

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

Another way



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



--

Regards,

Peo Sjoblom


"Sweetetc" <Sweetetc@discussions.microsoft.com> wrote in message
news:39A39331-CAB4-44C1-BA01-2FE14CEAC6E6@microsoft.com...
>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

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

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

these 3 formulas are not working for me

Post your own thread. It is against forum rules to add a "new" post to an existing one.