conditional formatting formula to highlight items on column B inrelation to column A

H

hombreazul

Hello,

I have two columns. Column one identifies a customer. Column two identifies an order. I would like conditional formatting which highlights the instances where there are multiple instances of orders from the same customer.I was able to create COUNTIF(A:A,A2)>1 to find duplicates in column one and I thought, since it works out to TRUE or FALSE (1,0), I could use this feature in an IF. So I wrote =(IF(COUNTIF(A:A,A2)>1*B2>0,1,0)) and got stuck. Clearly that does not work in any way.

I am out of my depth and would appreciate one of you gurus setting me straight. Thank you.



1524526 3049099
1524526 3049056
1524526 3049057
1524526 3049098
1524526 3049058
1524526 3049059
1524526 3049064
1524526 3049060
1524526 3049065
1524526 3049061
1524526 3049062
1524526 3049063
1524527 3049064
1524528 3049065
1524528 3049066
1524529 3049067
1524530 3049068
1524530 3049069
1524531 3049070
 
V

Victor Delta

Hello,

I have two columns. Column one identifies a customer. Column two
identifies an order. I would like conditional formatting which highlights
the instances where there are multiple instances of orders from the same
customer. I was able to create COUNTIF(A:A,A2)>1 to find duplicates in
column one and I thought, since it works out to TRUE or FALSE (1,0), I could
use this feature in an IF. So I wrote =(IF(COUNTIF(A:A,A2)>1*B2>0,1,0)) and
got stuck. Clearly that does not work in any way.

I am out of my depth and would appreciate one of you gurus setting me
straight. Thank you.



1524526 3049099
1524526 3049056
1524526 3049057
1524526 3049098
1524526 3049058
1524526 3049059
1524526 3049064
1524526 3049060
1524526 3049065
1524526 3049061
1524526 3049062
1524526 3049063
1524527 3049064
1524528 3049065
1524528 3049066
1524529 3049067
1524530 3049068
1524530 3049069
1524531 3049070

Concatenate the two numbers into a third cell on each row and then use
conditional formatting with the formula COUNTIF(C:C,C2)>1 [assuming the new
cell is in column C].

Hope this helps,

V
 
S

Spencer101

hombreazul;1512090 said:
Hello,

I have two columns. Column one identifies a customer. Column tw
identifies an order. I would like conditional formatting whic
highlights the instances where there are multiple instances of order
from the same customer. I was able to create COUNTIF(A:A,A2)>1 to fin
duplicates in column one and I thought, since it works out to TRUE o
FALSE (1,0), I could use this feature in an IF. So I wrot
=(IF(COUNTIF(A:A,A2)>1*B2>0,1,0)) and got stuck. Clearly that does no
work in any way.

I am out of my depth and would appreciate one of you gurus setting m
straight. Thank you.



1524526 3049099
1524526 3049056
1524526 3049057
1524526 3049098
1524526 3049058
1524526 3049059
1524526 3049064
1524526 3049060
1524526 3049065
1524526 3049061
1524526 3049062
1524526 3049063
1524527 3049064
1524528 3049065
1524528 3049066
1524529 3049067
1524530 3049068
1524530 3049069
1524531 3049070
Click to expand...

I presume the order numbers in column 2 are unique? If so, I'm unsur
as to what you're trying to acheive with the second formula.

If you could explain that a little more I'm sure you'll get the help yo
need
 
D

Don Guillett

I presume the order numbers in column 2 are unique? If so, I'm unsure
as to what you're trying to acheive with the second formula.

If you could explain that a little more I'm sure you'll get the help you
need.
Click to expand...
try
=IF(SUMPRODUCT(($A$2:$A$20=A4)*($B$2:$B$20>1))>1,1,0)
 
D

Don Guillett

I presume the order numbers in column 2 are unique? If so, I'm unsure
as to what you're trying to acheive with the second formula.

If you could explain that a little more I'm sure you'll get the help you
need.
Click to expand...

=IF(SUMPRODUCT(($A$2:$A$20=A4)*($B$2:$B$20>1))>1,1,0)
 
Top