Prev: VLOOKUP/HLOOKUP Query
Next: count text occurance in range
From: Richhall on 6 Jan 2010 07:30
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


From: Lars-�ke Aspelin on 6 Jan 2010 07:49
On Wed, 6 Jan 2010 04:30:09 -0800 (PST), Richhall
<rje.hall(a)yahoo.co.uk> wrote:

>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
From: Richhall on 6 Jan 2010 08:06
Unfortunately I am on Excel 2003 so unable to do it this way.

From: Lars-�ke Aspelin on 6 Jan 2010 08:24
On Wed, 6 Jan 2010 05:06:36 -0800 (PST), Richhall
<rje.hall(a)yahoo.co.uk> wrote:

>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
From: Max on 6 Jan 2010 09:35
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)
--
Max
Singapore
"Richhall" <rje.hall(a)yahoo.co.uk> wrote in message
news:dee87fa3-445d-4eb1-a38f-fac5365b6daa(a)a15g2000yqm.googlegroups.com...
> Unfortunately I am on Excel 2003 so unable to do it this way.
>


 |  Next  |  Last
Pages: 1 2
Prev: VLOOKUP/HLOOKUP Query
Next: count text occurance in range