So if A1 matches A7, but E1 doesn't match E7, then highlight it?
It sounds like you're just asking to highlight the unique combinations in A and
E.
Is that right?
So if I had:
a 2
b 1
c 3
a 2
b 2
c 3
In A1:A6 and E1:E6, then
a 2
b 1 <-- highlight this one
c 3
a 2
b 2 <-- highlight this one
c 3
Since those are the only ones that are unique.
If that's what you want, I could use this:
select the range to format|Conditional format
with A1 the activecell:
Formula is: =(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))=1
=======
If, on the other hand, I were looking for duplicates, with the same data in
A1:E6, I'd end up with:
a 2 <-- highlight this one
b 1
c 3 <-- highlight this one
a 2 <-- highlight this one
b 2
c 3 <-- highlight this one
I'd use this formula:
=(SUMPRODUCT(--($A1=$A$1:$A$6),--($E1=$E$1:$E$6)))1
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
Nick wrote:
Ok, so I understand conditional formatting basics, but this seems over my head.
I want to highlight the row of data if the following parameters are met
Col C (names) Col E (usernames)
If rowXCol C matches rowYCol C AND NOT rowxCol E matches rowyCol E then
Highlight rows x and y
I would like to be able to compare rowXColC to the entire column C to find
matches, but rowXColE only needs to search the rows that match in ColC
Does this make sense?
Thanks in advance.
--
Dave Peterson