Conditional formatting and multiple duplicate handling | vlookup | countif

[veespec]

Hello,

I have been making an excel sheet and have a question in regards with conditional formatting and duplicate handling.

I want to be able to use conditional formatting to compare 2 columns but have the conditional formatting only highlight the amount of duplicates that exist in the lookup column. So if the same amount of duplicates exists 3 times in the lookup columns but 4 times in the highlighted column, that it only highlights 3 out of the 4 duplicates.

Currently if it finds a single value it will highlight all the duplicates after a single match in the lookup column.

I can imagine this can be done using vlookup and countif but not having much luck asking google.

I'm using the below rules currently but they do not handle the duplicates how i would like.

=NOT(ISNA(VLOOKUP(N1,$G:$G,1,FALSE)))

=ISNUMBER(MATCH(N1,$G$2:$G$1000,0))

Thank you in advance for the help.

Richard

 

[DanteAmor]

1. Select both columns
2. Menú Conditional Formating
3. Highlight Cells Rules
4. Duplicates Values
5. Ok
6. The duplicates have been highlighted


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffc7ce; ">a</td><td style="background-color:#ffc7ce; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="background-color:#ffc7ce; ">b</td><td >e</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >c</td><td >f</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >d</td><td >g</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffc7ce; ">a</td><td style="background-color:#ffc7ce; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffc7ce; ">b</td><td style="background-color:#ffc7ce; ">b</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >m</td><td style="background-color:#ffc7ce; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffc7ce; ">a</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >z</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffc7ce; ">a</td><td > </td></tr></table>


Or check this
http://spreadsheetpro.net/comparing-two-columns-unique-values/

 

[veespec]

Hi,

Thanks for the help but this wont do it.

I don't need duplicates detected in just column A and B but to compare A with B and to detect the amount of duplicates as well.

See in your example, in B there is 3x "a" and in A there is 4x "a"

Right now i have to add conditional formatting to $B:$B with this rule =ISNUMBER(MATCH(B1,$A$2:$A$1000,0)) and to A with this rule =ISNUMBER(MATCH(A1,$B$2:$B$1000,0))

But doing the above doesn't compare and highlight the amount of duplicates across the columns.

What im trying to achieve is for it to be able to detect when (using your data), in B there is 3x "a" and in A there is 4x "a" and it only highlights 3 in both columns and leaves a single "a" in column A un highlighted

1. Select both columns
2. Menú Conditional Formating
3. Highlight Cells Rules
4. Duplicates Values
5. Ok
6. The duplicates have been highlighted

	A	B
e
c	f
d	g
m
z

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:76.04px;"><col style="width:76.04px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="bgcolor: #ffc7ce"]b[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="bgcolor: #ffc7ce"]b[/TD]
[TD="bgcolor: #ffc7ce"]b[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #ffc7ce"]a[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]
[TD="bgcolor: #ffc7ce"]a[/TD]

</tbody>

Or check this
http://spreadsheetpro.net/comparing-two-columns-unique-values/

 

[DanteAmor]

Something like this:
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >a</td><td style="background-color:#92d050; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >b</td><td >d</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >c</td><td >d</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="background-color:#ffff00; ">d</td><td style="background-color:#92d050; ">d</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="background-color:#ffff00; ">a</td><td style="background-color:#92d050; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#ffff00; ">b</td><td style="background-color:#92d050; ">b</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >m</td><td style="background-color:#92d050; ">a</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="background-color:#ffff00; ">a</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >z</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="background-color:#ffff00; ">a</td><td > </td></tr></table>

Column A
=COUNTIF(A1:$A$10,A1)-COUNTIF($B$1:$B$7,A1) < = 0
Applies to: =$A$1:$A$10

Column B
=COUNTIF(B1:$B$7,B1)-COUNTIF($A$1:$A$10,B1) < = 0
Applies to: =$B$1:$B$7

 

[Marcelo Branco]

Maybe this


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Value1​

[/td][td]

Value2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td="bgcolor:#DCE6F1"]

b​

[/td][td="bgcolor:#DCE6F1"]

d​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

c​

[/td][td]

d​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td="bgcolor:#DCE6F1"]

d​

[/td][td]

y​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td]

b​

[/td][td="bgcolor:#DCE6F1"]

b​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

m​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td="bgcolor:#DCE6F1"]

a​

[/td][td]

k​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

x​

[/td][td="bgcolor:#DCE6F1"]

z​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

a​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

a​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td="bgcolor:#DCE6F1"]

z​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

14​

[/td][td]

z​

[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

15​

[/td][td][/td][td][/td][/tr]
[/table]

Formula in CF to A2:A100
=AND(A2<>"",COUNTIF(A$2:A2,A2)<=COUNTIF(B$2:B$100,A2))

Formula in CF to B2:B100
=AND(B2<>"",COUNTIF(B$2:$B2,B2)<=COUNTIF(A$2:A$100,B2))

Hope this helps

M.

 

[veespec]

Hello,

Both examples work.

thank you, this was a bit beyond me.

Rich

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.