Conditional formating-how I could accomplish that?

Hello everyone.

I am trying to check if to columns in a table have the same values. If they are not all the same, a remote cell that is not part of the table should turn red. I tried "=NOT(A1:A99=B1:B99)" that works good as a true and false comparison but doesn't work for the conditional formating...

I am comfortable using formulas for conditional formating but this one seems to be tricker than I thought

Anyone has any idea how I could accomplish that?

Thanks

Give this a try

=SUMPRODUCT(--($A$1:$A$99<>$B$1:$B$99))

Originally Posted by Cutter

Give this a try

=SUMPRODUCT(--($A$1:$A$99<>$B$1:$B$99))

Thanks Cutter. I just changed the formula to be False/True and it worked!

=SUMPRODUCT(--($A$1:$A$99<>$B$1:$B$9))>0

Thanks for the quickly reply.

I used the formula I gave you in CF and it works just fine.

I used the formula I gave you in CF and it works just fine.

Sorry you are perfectly right. Your formula works just as it should.

On question if you still have the time. What the "--" in =SUMPRODUCT(--($A$1:$A$99<>$B$1:$B$9)) are for?

Thanks

That is called double unary. It converts TRUE / FALSE values to numeric values 1 / 0.

Originally Posted by Cutter

That is called double unary. It converts TRUE / FALSE values to numeric values 1 / 0.

Thank you, that was a first.

Hi everyone,

I am trying to vlookup the numbers in column titled branch to return same for cif but excel is returning the first cif number where the branch numbers are duplicated as follows:

Branch Cif Excel Results (Cif)
304620 308203 308203
304620 308055 308203
304620 306207 308203
452546 228901 228901
305552 302398 302398
305552 181381 302398
305239 306863 306863

Please what logical formulas can i use to get around this lack of unique numbers.

Many thanks for you assistance.

Folarin

Originally Posted by martindwilson

that formula isnt working fine! it is exactly the same as =a1=b1 dragged down 99 rows so 99 seperate conditional formats would be needed one for each cell

It does work. Not by checking if each row has the same value on both columns but by comparing the final result in both columns and checking if they are the same. As soon as the numbers are very unique and not 1 digit units it will be VERY difficult to get a fake TRUE because some numbers were mixed up.

Originally Posted by fodejimi

Hi everyone,

I am trying to vlookup the numbers in column titled branch to return same for cif but excel is returning the first cif number where the branch numbers are duplicated as follows:

Branch Cif Excel Results (Cif)
304620 308203 308203
304620 308055 308203
304620 306207 308203
452546 228901 228901
305552 302398 302398
305552 181381 302398
305239 306863 306863

Please what logical formulas can i use to get around this lack of unique numbers.

Many thanks for you assistance.

Folarin

You are in the wrong thread but if you do something like the example below it will work.

# Branch Cif Excel Results (Cif)
1 304620 308203 308203
2 304620 308055 308203
3 304620 306207 308203
4 452546 228901 228901
5 305552 302398 302398
6 305552 181381 302398
7 305239 306863 306863

Originally Posted by martindwilson

nope a1:a99=b1:b99 does not work how you think see attached

It happened a misunderstanding. I was referring to =SUMPRODUCT(--($A$1:$A$99<>$B$1:$B$9)) when I said it look all at once. a1:a99=b1:b99 and a1=b1 I agree that they do exactly the same thing.

@mhhenn
This is solved, isn't it?
If so, please mark your thread as SOLVED (click on the FAQ link at top of page for directions if needed).