Finding close text matches from a list of values

    Hi, I would like to find the next closest text matches. Say for example a formula in my worksheet produces the following text string 800 x 800 60 FF and I have a list of values that contained the following

    720 x 720 60 FF

    770 x 770 60 FF

    820 x 820 60 FF

    870 x 870 60 FF


    I would like to highlight we have both 770 x 770 60 FF and 820 x 820 60 FF as standard items.

    I don't mind solutions that are based formulas or VBA.

    Try,


    Select A2:A5 >> Conditional Formatting >> New rule >> Use a formula ......>>



    >> The rule formula enter :

    =OR(0+LEFT(A2,3)=MAX(IF(LEFT(A$2:A$5,3)<=LEFT(D$2,3),0+LEFT(A$2:A$5,3))),0+LEFT(A2,3)=MIN(IF(LEFT(A$2:A$5,3)>=LEFT(D$2,3),0+LEFT(A$2:A$5,3))))


    >> OK


Participate now!

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

Register Yourself Login