match cells in excel with all characters same except one

    Hi
    I am trying to work a formulae in excel which will do the following for me. I have data in two columns ,col A and col B. So col A could read like this


    A B
    a 1
    a 2
    a 7
    b 4
    b 5
    c 1
    c 3
    In need excel to check the col A for any values repeating themselves, so in this case "a" repeats itself thrice. It should then check the corresponding cells in col B and check if any of the values are less than 2 and any of the values is greater than 5. If it is then it should return true. So in the first case values corresponding to "a" are 1 , 2 and 7 .Since 1 and 7 satisfy the conditions it should give me true. But for b the values are 4 and 5 . Both the conditions are not satisfied and it should throw false. For c only one condition is met and again it should give false. Hope I have explained myself clearly

    Re: match cells in excel with all characters same except one


    Hi Rober thank you for your reply. However the formulae gives me true even for c(pls look the thread above). Also when the value is not repeating itself it still gives me true if the value in the corresponding cell is less than 2 or greater than 5 . I hope I am clear . Please sort this out for me it will be a big help.

    Re: match cells in excel with all characters same except one


    in your first post you have stated:


    Quote

    For c only one condition is met and again it should give false. Hope I have explained myself clearly


    that should have been NOT met.


    Try this:
    =IF(COUNTIF($A$2:$A$9,A2)=1,FALSE,(COUNTIFS($A$2:$A$9,C2,$B$2:$B$9,"<2")>0)+(COUNTIFS($A$2:$A$9,C2,$B$2:$B$9,">5")>0)=2)

Participate now!

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

Register Yourself Login