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

1. ## 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

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

3. ## Re: Compare the 1st 9 digits in two columns looking for duplicates

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

Vaya con Dios,
Chuck, CABGx3

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

4. ## RE: 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

5. ## Re: 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" <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

6. ## RE: 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

7. ## RE: 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

8. ## RE: 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

9. ## RE: 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

10. ## RE: 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

11. ## Re: Compare the 1st 9 digits in two columns looking for duplicates

these 3 formulas are not working for me

12. ## Re: Compare the 1st 9 digits in two columns looking for duplicates

22345678922223200 12345678922223200 false false false
32345678922223200 12345678922223200 false false false
42345678922223200 22345678922223200 false false false
12345678922223200 12345678922223200 false false false
12345678922223200 12345678922223200 false false false
24345678922223200 12345678922223200 false false false

22345678922223200 12345678922223200 =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
32345678922223200 12345678922223200 =countif(\$b\$1:\$b\$50,left(a2,9)&"*")>0 =countif(\$b\$1:\$b\$50,left(a2&" ",9)&"*")>0 =countif(b\$1:b\$100,left(a2,9)&"*")>0
42345678922223200 22345678922223200 =countif(\$b\$1:\$b\$50,left(a3,9)&"*")>0 =countif(\$b\$1:\$b\$50,left(a3&" ",9)&"*")>0 =countif(b\$1:b\$100,left(a3,9)&"*")>0
12345678922223200 12345678922223200 =countif(\$b\$1:\$b\$50,left(a4,9)&"*")>0 =countif(\$b\$1:\$b\$50,left(a4&" ",9)&"*")>0 =countif(b\$1:b\$100,left(a4,9)&"*")>0
12345678922223200 12345678922223200 =countif(\$b\$1:\$b\$50,left(a5,9)&"*")>0 =countif(\$b\$1:\$b\$50,left(a5&" ",9)&"*")>0 =countif(b\$1:b\$100,left(a5,9)&"*")>0
24345678922223200 12345678922223200 =countif(\$b\$1:\$b\$50,left(a6,9)&"*")>0 =countif(\$b\$1:\$b\$50,left(a6&" ",9)&"*")>0 =countif(b\$1:b\$100,left(a6,9)&"*")>0

13. ## Re: Compare the 1st 9 digits in two columns looking for duplicates

ive used show formulas in column d1 e1 f1

14. ## Re: Compare the 1st 9 digits in two columns looking for duplicates

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1