Match values in more than one column

[Richhall]

Hi

I have three columns of data and wish to highlight the matches or
change the format if there are any duplicates.

i.e

A B C

Apple Banana Cheese
Banana Orange Milk
Chocolate Milk Water
Biscuits Pear Bread
Wafers Plum Banana

So Banana would be highlighted in all, Milk in B and C. I assume I'd
use conditional formatting, but the MATCH function only seems to let
me compare against 1 column, not a full range.

Is there a simple way of doing this, or do I need some sort of
function to add together the matches and then conditional format?

If this can be done in VB that is fine as there is a script that
creates the lists so I could append to that.

Cheers

Rich

 

[Lars-Åke Aspelin]

Hi

I have three columns of data and wish to highlight the matches or
change the format if there are any duplicates.

i.e

A B C

Apple Banana Cheese
Banana Orange Milk
Chocolate Milk Water
Biscuits Pear Bread
Wafers Plum Banana

So Banana would be highlighted in all, Milk in B and C. I assume I'd
use conditional formatting, but the MATCH function only seems to let
me compare against 1 column, not a full range.

Is there a simple way of doing this, or do I need some sort of
function to add together the matches and then conditional format?

If this can be done in VB that is fine as there is a script that
creates the lists so I could append to that.

Cheers

Rich

If you have Excel 2007 this is easily achived by Conditional
Formatting that you find it in the Styles section of the Home tab.
Select the entire table then choose Conditional Formatting ->
Highlight Cell Rules -> Duplicate Values

Hope this helps / Lars-Åke

 

[Richhall]

Unfortunately I am on Excel 2003 so unable to do it this way.

 

[Lars-Åke Aspelin]

Unfortunately I am on Excel 2003 so unable to do it this way.

Then try this macro:

Sub highlight_duplicates(r As Range)
For Each c1 In r
duplicate_found = False
For Each c2 In r
If (c1.Address <> c2.Address) And c1.Value = c2.Value Then
duplicate_found = True
End If
Next c2
If duplicate_found Then
c1.Interior.ColorIndex = 3
Else
c1.Interior.ColorIndex = 0
End If
Next c1
End Sub


Sub test()
highlight_duplicates ActiveSheet.Range("A1:C5")
End Sub

Hope this helps / Lars-Åke

 

[Max]

Think COUNTIF will work over a rectangular range
Select the source range, which is assumed A1:C5 (with A1 active)
Apply CF, using Formula is: =COUNTIF($A$1:$C$5,A1)>1
Format to taste > OK out
(Banana & Milk will be triggered)

 

[Richhall]

Lars-Åke, thank you that worked brilliantly, amended slightly to put
in a longer script:

Dim r As Range

Set r = Worksheets("Sheet1").Range("DuplicateRange")

For Each c1 In r
duplicate_found = False
For Each c2 In r
If (c1.Address <> c2.Address) And c1.Value = c2.Value Then
duplicate_found = True
End If
Next c2
If duplicate_found Then
c1.Font.Italic = True
c1.Font.Bold = True
Else
c1.Font.Italic = False
c1.Font.Bold = False
End If
Next c1

Max, thank you had already used Lars-Åkes post, so not tried that way.

 

[Max]

Max, thank you had already used Lars-Åkes post, so not tried that way.

No problem. It doesn't take more than 10 seconds of your time to try that
option, though.