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

=SUM(COUNTIFS(\$A\$2:\$A\$9,D1,\$B\$2:\$B\$9,{">5","<2"}))>0
Where D1=a

This will work in Excel >03

• 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

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

Hi Robert I have attached a file showing where exactly the formulae is not giving appropriate result . Plz help

Files

• 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)

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

Hi Robert this is fantastic thankyou so very much

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

You are welcome.

Participate now!

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