conditional format with 2 diff columns

bigdan

Well-known Member
Joined
Oct 5, 2009
Messages
846
Office Version
  1. 2013
Platform
  1. Windows
i want conditional format to look at both columns rather than just one. can this not be done? i cant figure out how. well with a formula MAYBE but that's complicatign things, and even that not sure.

lets say this is my data (the numbers and words are adjacent columns, just having trouble with pasting).

<TABLE style="WIDTH: 95pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=127 border=0><COLGROUP><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2304" width=63><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 47pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=63 height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Hello</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hi</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">John</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>7</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">John</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>8</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hello</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Hello</TD></TR></TBODY></TABLE>

I'd like duplicates to get highlighted. The only duplicate (for this purpose) would be the first and last set, ie 5&Hello not just 5's, or John's, or Hello's.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
What version of excel are you using? I know that in 2010 you can specify in the conditional formatting to format duplicate values, so you would just select the two columns to have the formatting apply to and then specify you want to format duplicates in whatever style you want.
 
Upvote 0
With a helper column (that can be hidden)

Excel Workbook
ABC
15Hello5-Hello
25Hi5-Hi
36John6-John
47John7-John
58Hello8-Hello
65Hello5-Hello
Sheet2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11. / Formula is =COUNTIF($C:$C,$C1)>1Abc
B11. / Formula is =COUNTIF($C:$C,$C1)>1Abc
 
Upvote 0
Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;background-color: #C5D9F1;;">5</td><td style="background-color: #C5D9F1;;">Hello</td><td style="text-align: right;;"></td><td style="background-color: #C5D9F1;;">CF - =SOMA(--($A1&$B1=$A$1:$A$6&$B$1:$B$6))>1</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">5</td><td style=";">Hi</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">5</td><td style=";">John</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">7</td><td style=";">Hi</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">8</td><td style=";">Hello</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;background-color: #C5D9F1;;">5</td><td style="background-color: #C5D9F1;;">Hello</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">******</td><td style=";">******</td><td style=";">******</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />
Markmzz
 
Upvote 0
With a helper column (that can be hidden)



<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #ff0000; BORDER-BOTTOM-COLOR: #ff0000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #ff0000; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #ff0000; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Conditional formatting </TD></TR><TR><TD><TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=0 border=1><TBODY><TR><TD>Cell</TD><TD>Nr.: / Condition</TD><TD>Format</TD></TR><TR><TD>A1</TD><TD>1. / Formula is =COUNTIF($C:$C,$C1)>1</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR><TR><TD>B1</TD><TD>1. / Formula is =COUNTIF($C:$C,$C1)>1</TD><TD style="BACKGROUND-COLOR: #ffff00">Abc</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Thanks for this. :)
I got the first two but not sure what's happening here. What's >1 got to do w/ this?

Actually I'm not sure I'm even understanding the formula. Shouldnt the second parameter be a condition?


Maybe this:




Excel 2007<TABLE style="BORDER-RIGHT: #a6aab6 1px solid; BORDER-TOP: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BORDER-BOTTOM: #a6aab6 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #e0e0f0; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Hello</TD><TD style="TEXT-ALIGN: right"></TD><TD style="BACKGROUND-COLOR: #c5d9f1">CF - =SOMA(--($A1&$B1=$A$1:$A$6&$B$1:$B$6))>1</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">5</TD><TD>Hi</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">5</TD><TD>John</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">7</TD><TD>Hi</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">8</TD><TD>Hello</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">6</TD><TD style="BACKGROUND-COLOR: #c5d9f1; TEXT-ALIGN: right">5</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Hello</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">8</TD><TD>******</TD><TD>******</TD><TD>******</TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>
Sheet1



Markmzz


Thanks!
What's "CF - " ?
What's the SOMA?
After SOMA there's a -- inside the parenthesis. Is that intentional?
 
Upvote 0
Thanks!
What's "CF - " ?
What's the SOMA?
After SOMA there's a -- inside the parenthesis. Is that intentional?

Bigdan,

Lets go:

1) CF - Conditional Format

2) SOMA is SUM function (I forgot the translation).

What version of Excel do you have?

Markmzz
 
Last edited:
Upvote 0
Sorry, I forgot this:

"After SOMA there's a -- inside the parenthesis. Is that intentional?"

In a simple way, the -- is to transform true or false in 1 or 0.

--{True; False} -> {1;0}

Then Sum (--{True; False}) = Sum({1;0}) = 1

In the example in the rows 1 and 6 we have:

=SUM({1;0;0;0;0;1})=2>1

I holpe this help.

Markmz
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top