Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
On Wed, 6 Jan 2010 04:30:09 -0800 (PST), Richhall
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
Unfortunately I am on Excel 2003 so unable to do it this way.
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
On Wed, 6 Jan 2010 05:06:36 -0800 (PST), Richhall
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
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" wrote in message ... Unfortunately I am on Excel 2003 so unable to do it this way. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match values in more than one column
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. -- Max Singapore |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If two criteria match then sum matching values in another column | Excel Worksheet Functions | |||
match values in two column and return value in | Excel Worksheet Functions | |||
MATCH A NUMBER AS THE SUM OF VALUES IN A COLUMN | Excel Worksheet Functions | |||
How to match values in one column to values in another? | Excel Worksheet Functions | |||
match 2 databases based on a column with same values | Excel Discussion (Misc queries) |