tricky conditional formatting!
sample text.xlsx
Im searching for the numbered pet where the pet number is exclusive and, the animal1 and animal2 columns are not a match.
In this example, Pet 6, would be the only highlighted value; because there are no other pets numbered 6, and animal 1 and animal 2 do not equal each other.
Does this make sense? :O)
Select the range, from A2:C14 and apply conditional format formula:
=AND(COUNTIF($A$2:$A$14,$A2)=1,$B2<>$C2)
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Hi Wannabe_guru,
See the attached file where I have used the following logic in conditional formatting on row 13 and replicated on other rows:-
=AND(COUNTIF($B:$B,$B13)=1,$B13<>$C13)
sample text.xlsx
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), [email protected]
Now is this possible, say, if i wanted to make a column that said, if it meets those requirements = true or false instead of highlighting?
Hi wannabe_guru,
Use below formula in D2 and drag it down:--
=AND(COUNTIF($B:$B,$B2)=1,$B2<>$C2)
sample text.xlsx
Regards,
DILIPandey
<click on below 'star' if this helps>
great! thanks everyone :D
Cheers
Please mark this thread as [SOLVED]... thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
actually - we missed something here ...
it needs to show not only the false, but the absolute false ... where the pet may not be exclusive & animal1 and animal2 don't match.
so, for example.
pet 6 cat dog
pet 6 mouse cat
In this case, both of these would meet the criteria.sample text.xlsx
here is an updated file.
Last edited by wannabe_guru; 05-04-2012 at 10:47 AM.
Originally Posted by NBVC
somehow, we have to modify this slightly to incorporate for the pets that have the same number, but different animal1 & animal2.
Do you mean?
=AND(COUNTIF($A$2:$A$14,$A2)>1,$B2<>$C2)
nope, that doesn't work. - what should happen is the only "true" statements should be pet 6,6,7.
somehow it must meet this criteria...
if there is only one pet & both animal1 and animal2 are different, then the statement result should be true.
if there is more than one pet and if and animal1 and animal2 are different for all of the pets, then the statement result should be true.
So really you just want to know if animal1 and animal2 are different... since it doesn't seem to matter if the the pet is unique or not?
All you would need then is:
=$B2<>$C2
or re-reading your last statements... perhaps this?
=SUMPRODUCT(($A$2:$A$14=$A2)*($B$2:$B$14=$C$2:$C$14))=0
this will only highlight the pet if all of the animal1 are different from the animal2 for the unique pet.
Last edited by NBVC; 05-07-2012 at 09:27 AM.
we do need to know unique pets, but we also need to know when a pet isn't unique & if both animals are different. so we are looking at two different cases.
if pet is unique and animal1 <> animal2, then true
if pet is not unique, then animal 1 <> animal2, to be true iff all other pets in same class and animal1 <> animal2; if any pets in same class where animal1=animal2, then it is still false.
I edited my above post with another formula... try that to see if it works for you.
you are so amazing. ugh!!! i want your wisdom.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks