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


0
Richhall
1/6/2010 12:30:09 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

6 Replies
1702 Views

Similar Articles

[PageSpeed] 40

On Wed, 6 Jan 2010 04:30:09 -0800 (PST), Richhall
<rje.hall@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
0
Lars
1/6/2010 12:49:55 PM
Unfortunately I am on Excel 2003 so unable to do it this way.

0
Richhall
1/6/2010 1:06:36 PM
On Wed, 6 Jan 2010 05:06:36 -0800 (PST), Richhall
<rje.hall@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
0
Lars
1/6/2010 1:24:27 PM
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@yahoo.co.uk> wrote in message 
news:dee87fa3-445d-4eb1-a38f-fac5365b6daa@a15g2000yqm.googlegroups.com...
> Unfortunately I am on Excel 2003 so unable to do it this way.
> 


0
Max
1/6/2010 2:35:34 PM
Lars-=C5ke, thank you that worked brilliantly, amended slightly to put
in a longer script:

Dim r As Range

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

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

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

0
Richhall
1/6/2010 7:08:12 PM
> 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 


0
Max
1/6/2010 11:13:24 PM
Reply:

Similar Artilces:

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

column charts on two axis
I am trying to show a column chart on 2 axis. I have chosen the combination line and column chart to get the 2 axis but when I change the line to a second column it puts the columns over the top of the first axis columns. How do I get them to be side by side? Have a look at: http://peltiertech.com/Excel/Charts/ColumnsOnTwoAxes.html best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jo" <Jo@discussions.microsoft.com> wrote in message news:F3800842-2343-4319-8496-4D8CAC0DABFF@microsoft.com... >I am trying to show a column chart on 2 axi...

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

MDI app with different CFrameWnd, searching one
Hello NG, in my MDI application, I use several different CChildFrame windows with its CDocument and CView. CPersonFrame* mit CPersonDoc und CPersonView CAdressesFrame* mit CAdressesDoc und CAdressesView CPreferencesFrame* mit CPreferencesDoc und CPreferencesView The PreferencesFrame and AdressesFrame should be opened only once at a time in my programme. The PersonFrame is a single window concerning each person in the AdressesFrame. Now I want to open a special window in my application menu. If it's already opened, it should appear in front. So I first look for the child window if it is...

Change default search to Value, not formula?
Every time I start a word search, the default searches for fomulas instead of value. So everytime I have to open up the option button and change it to value instead of formula. How do I change that default setting? lbbss Saved from a previous post: Excel tries to help by remembering the last settings you used--except for the first search in that session. You can use that to your advantage. You could make a dummy workbook and put it in your xlStart folder. Have a macro in that workbook that does a find (and sets all the stuff the way you like). Then closes and gets out of the way. ...

Conditional format for unique values
Hello, I would like to set a conditional format to color each unique value a different color. It will also be applied to multiple occurences of the value. The problem is that the values are dynamic and may not always be the same number of occurences, in the same cell, etc. I have used the formula IF(ISNUMBER(COUNTIF($F$3:F3,F3)),ROW(),"")) to try to identify the occurences but creating a conditional format has proven out of my league! Please help! And thanks!! Conditional formats are limited to 3 different formats in XL2003 or less. So you can end up with a max of ...

Sum of every nth cell in a column
I am trying to figure out the formula for adding the nth cell in a given column. I am using the formula below to add every 4th cell but it is just one that I found on line. Can someone explain to me the components of this formula so I can adapt it? =SUM((C2:C213) * (MOD( ROW( C2:C213)+1, 4)=0)) That is an array formula so it must be committed with Shift + Ctrl + <Enter>. here is how it works... Moving down through cells C2:C213 it looks at each 2 things. The value of the cell and the row that cell is on. If the row that cell is on is +1 is evenly divisible by 1 then ...

How do I count one cell based on another cell?
How do you count the number of cells with data (not blank) in a given range based on a criteria in a different range? for example, how many corresponding cells are populated with data in c1:c1000, if b1:b1000 = SMU? I have tried so many variations of the the following to no avail. =COUNT(IF(b1:b1000,"SMU",counta(c1:c1000))) If anyone can resuce me...I am frustrated...what am I missing? Harr Hi try =SUMPRODUCT(--(B1:B1000="SMU"),--(C1:C1000<>"")) Also see: http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards Frank Kabel Frankfurt, Germany &qu...

Can one obtain Business Contact Manager Seperately
Does anybody know if an add on is available to add the "Business Contact Manager" to a copy of Outlook 2003 that came pre-installed on my new Dell. Apparently you have to buy either Office Pro or one other addition that I cannot re-call to get this feature. There has to be a way to add it to my current Outlook 2003. I purchased Office Basic along with this Dell and I really could use the "Business Contact Manager" tool. No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) vir...

Multiply all values by 10
Is there a way to multiply all values in my spreadsheet by 10? If so, how? Type the 10 in some random cell and copy. Select your target cells and Edit > Paste Special, and select the Multiply radio button. "RTimberlake" wrote: > Is there a way to multiply all values in my spreadsheet by 10? If so, how? First, make a copy of the workbook in case you make a typo or something, then put 10 in an empty cell, copy it, then select all values needed and do edit>paste special and select multiply -- Regards, Peo Sjoblom (No private emails please) "RTimberlake" ...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

print only one page in a message Plain-Text
This is a multi-part message in MIME format. ------=_NextPart_000_014E_01C60ADA.6EC09160 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi everyone! I can print only one page when a received a message in HTML format, but, = when I received a message how Plain text, I can=B4t choose only one page = ... How can I to do this?? My outlook is 2003. ------=_NextPart_000_014E_01C60ADA.6EC09160 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4....

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

Change rows to columns for similar records
Hi - am trying to change rows to columns for similar records. How do I do that? e.g. Staff A Cellular No. XXXXX Staff A Office No. XXXX Staff B Cellular No. XXXXX Staff B Office No. XXXXX Wld like for it be in the following row Staff Cellular No Office No A XXXXX XXXXX B XXXXX XXXXXX Do advise. Thanks. one way would be Would be to set up your "heading" as such Cell Phone Staff A 1 2 Staff B 3 4 Then assuming that Staff A is in B8 Staff B is in B9 Cell is in C7 Phone is in D7 In cell c8 enter ...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

if value is greater than # * by 20% if less * by 40
if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% Try this: For A1: (a price) The marked up price B1: =A1*IF(A1>30,1.2,1.4) Does that help? *********** Regards, Ron XL2002, WinXP "sypher" wrote: > if price is higher than 30 dollars markup by 20% if less than 30$ markup by 40% ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

Column heading differences
The menu column headings on my spreadsheets today are numbers instead of letters! I want the letters back! I like the differentiation between rows (numbers) and columns (letters). Help?? I received and opened a virus-free spreadsheet from a co-worker. Her spreadsheet had numbers for the columns -- could that have made my default switch? How do I switch it back? <Tools> <Options> <General> tab, And *UNCHECK* "R1C1 Reference Style". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benef...

This forum does have value!
As year end approaches and I am considering upgrading to Money 2005, I found the message traffic extremely valuable. After this summers fiasco with the update to MSN Money locking us out of our local data file, I thought, or was hoping M$ would make an extra effort to make sure that Money 2005 was a significant improvement and without the assorted problems that I have found posted here. Some still unresolved. I have made my decision and I do not care how much work it is going to take me, bt I am going back to Quicken after giving Money a two year trial. This program is just not ready f...

How does MATCH handle a Non-Match?
I would like a steer on how the MATCH Worksheetfunction behaves if it doesn't find an exact match of values in the array it is searching. I am looking through a series of Dates and instead of picking the nearest one - which I need and seems most obvious - it seems to pick the previous one. I.E. - Searching for 14 Oct 2005 In a list which contains (among others) 15 Sep 2005 16 Oct 2005 And it picks the 15 Sep row! PS. Cell values are actually 14/10/2005 but formatted as above. In Excel's Help for the MATCH function, it describes how the values are returned. Chris wrote: > I w...

How to create a column styled blog in Word
I want to use word to create content for a blog that looks like a magazine with 2 columns - how would I create a column blog ...

Scatter chart without numerical values
For some time now, when I create a scatter chart from tabulated (numerical) data, the chart does not display the x/y values, but the serquence numbers instead. This is similar to a bar chart, but should'nt happen with a scatter chart. The scatter charts have worked ok in the past. I'm using Excel 2000 in a company office environment where I have little control over the Excel installation/set-up. -- Regards Fred Are you really making an XY chart? If you make a Line chart with two numeric columns AND the first row have labels, Excel thinks both columns are y-data and uses 1,2,3...