+ Reply to Thread
Results 1 to 14 of 14

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

  1. #1
    Sweetetc
    Guest

    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. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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. #3
    CLR
    Guest

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


  4. #4
    Ron Coderre
    Guest

    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. #5
    Peo Sjoblom
    Guest

    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. #6
    Sweetetc
    Guest

    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. #7
    Sweetetc
    Guest

    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. #8
    Ron Coderre
    Guest

    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. #9
    Sweetetc
    Guest

    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. #10
    Sweetetc
    Guest

    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. #11
    Registered User
    Join Date
    05-17-2016
    Location
    india
    MS-Off Ver
    2013
    Posts
    9

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

    these 3 formulas are not working for me

  12. #12
    Registered User
    Join Date
    05-17-2016
    Location
    india
    MS-Off Ver
    2013
    Posts
    9

    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. #13
    Registered User
    Join Date
    05-17-2016
    Location
    india
    MS-Off Ver
    2013
    Posts
    9

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

    ive used show formulas in column d1 e1 f1

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    22,614

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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