Match Each Digit in Columns, Return True or False if All Match

    I need to check Col A (four numbers) against Col B (six numbers) and return TRUE or FALSE if ALL value(s) in Col A match those in Col B. I greatly appreciate your help. Thank you in advance.


    A------B------C
    1234 615243 TRUE
    4132 264315 TRUE
    1238 351462 FALSE
    1244 413652 TRUE
    1222 341526 TRUE
    8888 562143 FALSE
    5555 165234 TRUE

    Re: Match Each Digit in Columns, Return True or False if All Match


    Perhaps

    Quote


    =NOT(ISERROR(FIND(MID(A1,1,1),B1)))*NOT(ISERROR(FIND(MID(A1,2,1),B1)))*NOT(ISERROR(FIND(MID(A1,3,1),B1)))*NOT(ISERROR(FIND(MID(A1,4,1),B1)))=1


    or better

    Quote

    =NOT(ISERROR(SUM(FIND(MID(A1,{1,2,3,4},1),B1))))

    Re: Match Each Digit in Columns, Return True or False if All Match


    PCI, Thank you very very much for your speedy assistance.
    You did in minutes what I've been trying to do for weeks.
    I appreciate your expertise. Thank you!


    Quote from PCI;549416

    Perhaps


    or better

    Re: Match Each Digit in Columns, Return True or False if All Match


    Hi PCI,
    In further testing the formula:
    I have found that if there is a number that begins with zero in Column A, it will return a TRUE statement which is incorrect.
    The zero also is made invisible by XL. Could you please show me how to fix this? Thank you.


    A-----B---------C
    0111 615243 TRUE <----Should Be FALSE
    0888 615243 FALSE

    Re: Match Each Digit in Columns, Return True or False if All Match


    Ok, with your file it's more clear: Numbers may have only 3 digits.
    Can they have less digits: 1 or 2 only
    In other words what the possible values: from 100 to 9999 ??

    Triumph without peril brings no glory: Just try

    Re: Match Each Digit in Columns, Return True or False if All Match


    Thank you, PCI. Everything is working well. Thank you once again for your time and help!

    Re: Match Each Digit in Columns, Return True or False if All Match


    But it means there is only 3 digits: If it is, change the formula to {1,2,3}
    Is it numbers or text?
    Is it still OK

    Triumph without peril brings no glory: Just try

    Re: Match Each Digit in Columns, Return True or False if All Match


    I've changed the formula. Everything is OK, even better : ) Thank you very much, PCI.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login