|
conditional formatting formula to highlight items on column B inrelation to column A
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*B20,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 |
conditional formatting formula to highlight items on column B in relation to column A
"hombreazul" wrote in message
news:7030854.43.1330650515895.JavaMail.geo-discussion-forums@vbbgt10... 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*B20,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 |
Quote:
If you could explain that a little more I'm sure you'll get the help you need. |
conditional formatting formula to highlight items on column Binrelation to column A
On Sunday, March 4, 2012 1:56:13 AM UTC-6, Spencer101 wrote:
hombreazul;1512090 Wrote: 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*B20,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 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. -- Spencer101 try =IF(SUMPRODUCT(($A$2:$A$20=A4)*($B$2:$B$201))1,1 ,0) |
conditional formatting formula to highlight items on column Binrelation to column A
On Sunday, March 4, 2012 1:56:13 AM UTC-6, Spencer101 wrote:
hombreazul;1512090 Wrote: 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*B20,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 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. -- Spencer101 =IF(SUMPRODUCT(($A$2:$A$20=A4)*($B$2:$B$201))1,1 ,0) |
| All times are GMT +1. The time now is 09:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com