how can compare data between two worksheets?

What's the best way to compare data between two worksheets and highlight the 
similarities?

Also say you are comparing a list of people's names but one worksheet has a 
column that has the person's full name preceded by title while the other list 
has separate columns for first and last name?  How could you compare the 
names in that situation?
0
Utf
2/4/2010 3:21:01 PM
excel 39879 articles. 2 followers. Follow

2 Replies
1355 Views

Similar Articles

[PageSpeed] 45

The way you have your sheets set up makes it difficult to be sure. The 
following macro will work but can give a wrong result (see below).

Sub t()
  Dim rngToSearch As Range, rngToCheck As Range
  Dim c, d, sName As String
  
  Set rngToSearch = Sheets("Sheet1").Range("A2:A20") 'change to suit
  Set rngToCheck = Sheets("Sheet2").Range("A2:A20")  'change to suit
  
  For Each c In rngToCheck
    sName = c & " " & c.Offset(0, 1) & "  "
    For Each d In rngToSearch
      If IsNumeric(Application.Search(sName, d)) Then
        If Len(c) > 0 Then
          c.Offset(0, 2) = "Found"
      Else
        c.Offset(0, 2) = "Not Found"
        End If
      End If
    Next d
  Next
End Sub

The macro will display the result in column C and assumes that the list only 
has two columns. If you want to enter the result in column F change the 
offset to (0,6).

To use the macro press ALT + F11 to open the VB Editor, Press Insert, Module 
and copy the code into the new module. Close the Editor and in the worksheet 
press ALT + F8, select the macro and click Run.

The problem occurs if the First Name column contains a name that is in the 
Full name list and the Last name is left empty.

Say the full name list includes Mr Martin Smith and the FName contains 
Martin and the LName is blank - in this case it is returned as 'Found', 
However, if a First Name is Martin and the Last name is Blundel then, unless 
he is somewhere in the Full Name List, he is declared 'Not Found'

HTH
Peter
"biff" wrote:

> What's the best way to compare data between two worksheets and highlight the 
> similarities?
> 
> Also say you are comparing a list of people's names but one worksheet has a 
> column that has the person's full name preceded by title while the other list 
> has separate columns for first and last name?  How could you compare the 
> names in that situation?
0
Utf
2/5/2010 3:50:01 PM
The following revided code may be better

Sub t()
  Dim rngToSearch As Range, rngToCheck As Range
  Dim c, d, sName1 As String, sName2 As String
  
  Set rngToSearch = Sheets("Sheet1").Range("A2:A20") 'change to suit
  Set rngToCheck = Sheets("Sheet2").Range("A2:A20")  'change to suit
  
  For Each c In rngToCheck
  
    sName1 = c
    sName2 = c.Offset(0, 1)
    For Each d In rngToSearch
    If Len(sName1 & sName2) = 0 Then Exit For
      If IsNumeric(Application.Search(sName1, d)) Then
        If IsNumeric(Application.Search(sName2, d)) Then
          c.Offset(0, 2) = "Found"
          Exit For
        End If
      Else
        c.Offset(0, 2) = "Not Found"
      End If
    Next d
  Next
End Sub

Peter

"biff" wrote:

> What's the best way to compare data between two worksheets and highlight the 
> similarities?
> 
> Also say you are comparing a list of people's names but one worksheet has a 
> column that has the person's full name preceded by title while the other list 
> has separate columns for first and last name?  How could you compare the 
> names in that situation?
0
Utf
2/5/2010 7:34:01 PM
Reply:

Similar Artilces:

Can't track certain Outlook Contacts
Hi, For one of my users (crm 3.0 - Outlook 2003), the 'Track in CRM' button does not appear for certain contacts. About 2/3s of the contacts are fine. I thought it might be contacts created in the Blackberry then sync'd to Outlook but that doesn't appear to be the case. I'm still working on finding a pattern, unfortunately this user is travelling a lot so it's hard for me to sit down with her and see what she's doing. I thought maybe someone may have had a similar experience. ...

Excel/Visio Form for entering data
I've seen this done before, just trying to figure out how to accomplish it. I have an Excel spreadsheet containing a Visio network diagram with text fields for entering data (IP addresses, etc). I'm trying to recreate this form with a new diagram. Once I've pasted the Visio diagram into Excel, I'm having difficulty getting Excel to allow me to specify fields and enter data. There has to be an easy way to do this. Anyone have a good resource or know how to do it? ...

How can I ...
I am now running Outlook 2003. Previously, I have run most versions of Outlook and I have spotted a difference I would rather not have. Whenever I click on a web link within a message, outlook opens a new instance of IE to view whatever is on the end of the link. Previous versions would re-use the first instance created rather than opening new instances all the time. Any suggestions as how whether (and how?) this behaviour can be restored to match the previous versions? Cheers. You can't. Sorry. -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using...

Can't Make Attachment
I just upgraded to outlook 2003 and now I can't drag and drop files into a new mail message; even though I can click on the paper click and attach a file that way. I had this functionality prior to upgrading to Outlook 2003. I am running Windows XP. Please advise if anyone knows of a setting for this or a registry fix. Thanks.........Jason Steeves ...

Copy field data to multiple places
Newbi here.... I have a access 07 file of about 1000 records (rows) and a field (column) I'll call the "project number". All the records do not have the project number inserted as of yet. Is there a simple means to insert a project number in say 50 records at a time, another project number in another 75 records etc. Copy/Paste will do it but may take months to enter. Any suggestions appreciated. TIA On Wed, 27 Feb 2008 15:31:05 -0500, "Meebers" <justme@idontkno.com> wrote: >Newbi here.... I have a access 07 file of about 1000 records (rows) and ...

referencing to worksheet names in macro for each new worksheet inserted
Hi I created a code to insert new worksheets and rename them according t values on the new worksheet itself. Say in Cell D1, i have th worksheet name. My question is when i want to refer to this worksheet in subsequen coding, how should i code it? For eg, How should i write the ???? for Sheets("????").select? Would creatin the a variable to store the names help? Thanks in advance Ken -- Message posted from http://www.ExcelForum.com After inserting your new worksheet set it's name equal to a variable. For example SHEETS.ADD VWORKSHEET = ACTIVESHEET.NAME This method ...

Where Can I Find The Icon For A New E-mail Message In Outlook?
I've created the shortcut "C:\Program Files\Microsoft Office\OFFICE11\OUTLOOK.EXE" /c ipm.note for my Outlook to open a new message but it assigns the Outlook Icon... Thanks Assuming you are using an English version Outlook 2003, you might find what you are looking for at: \Program Files\Microsoft Office\OFFICE11\FORMS\1033 "Justern" <justern@hotmail.com> wrote in message news:4133cc19$0$27218$61ce578d@news.syd.swiftdsl.com.au... > I've created the shortcut "C:\Program Files\Microsoft > Office\OFFICE11\OUTLOOK.EXE" /c ipm.note for m...

Can I see the summary report of each resouce's booking status ?
Here we using AutoAcceptAgent to booking resource account like meeting room. Is there any method to view the summary report ? for example, a summary report show which meeting room booked by which one for what agenda. I have tried to use public folder with email address, but can't register by AutoAcceptAgent. Any one has any idea of such request ? Pls help soon. Thanks. On Thu, 20 Jul 2006 19:14:02 -0700, Enid <Enid@discussions.microsoft.com> wrote: >Here we using AutoAcceptAgent to booking resource account like meeting room. >Is there any method to view the summary repor...

Pivot Tables & changing data
Hi - I have never used pivot tables in Excel before so hopefully what I am going to ask is possible and not too complicated for me.... :o) I am working with Excel 2003. I have a pivot table already set up and the information is pulling data from a row titled "sum of Subscriber". I added new data in a new column from the main spreadsheet and I would like to pull the data from there. Its titled "Adj Subscriber". Is it possible to switch it? If so, how? Thanks, Anna Marie Anna wrote: > Hi - I have never used pivot tables in Excel before so hopefully what I am &...

how to edit my x-axis data on a line graph
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel my x axis data on my graph is just showing numbers 1 2 3 4 5 6 7 8 9 10 11.... and it is suppose to show the years. I can't figure out how to change these values ...

filtering account numbers from a worksheet
I'm new to excel. I have created a spreadsheet containing my newspaper's subscriber list, containing account numbers and addresses. I want to merge another list of subscribers(with account numbers and addresses) with the first list to scrub those addresses, etc. from that list. Can anyone advise me? Hi not completely sure what you're trying to do. But as a starting point: http://www.cpearson.com/excel/duplicat.htm#InOneNotOther http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany fouraker wrote: > I'm new to excel. I...

How do I modify an existing worksheet to remove columns & contents
I am working with a very large spreadsheet and want to modify it using only certain columns and data in order to keep from re-doing the entire worksheet. ANybody out there in cyberspace got suggestions or good reference sources I can use? Gil There are just too many scenarios to ponder without getting some more details on what you wish to do. Please be a little more specific in your description. Gord Dibben Excel MVP On Wed, 9 Feb 2005 18:15:06 -0800, "Gil Gray" <Gil Gray@discussions.microsoft.com> wrote: >I am working with a very large spreadsheet and want to m...

Can not install Office Ultimate 2007 because of a setup error mess
Hello group, I have a desktop with no Office Suite which I built with Windows XP Pro SP2 (Version 2002), from a WGA Kit. I ordered the back-up discs (2 pcs) Office Ultimate 2007 from digital river.com store and tried installing in this machine but I get this message: "Setup error: The language of this installation package is not supported by your system." I tried it on the 2nd DVD drive of the same machine but same result. I tried installing the Office software in a Sony Vaio laptop and it seems it will install properly and I discontinued the setup. Any information/ass...

Where can I get UNLIMITED Excel stock qoutes?
The Excel Add-in that allows you to retrieve stock qoutes is limited in taht it only allows you to get about 250 qoutes at a time. How can I get unlimited qoutes in a format compatible with excel? Z There's a program called Historical Stock Quotes which can create tab or comma delimited files that can be read into Excel. If you want something that updates automatically within Excel, you may have to buy an add-in. Have you searched the web? On Sun, 6 Mar 2005 12:18:23 -0800, ZouBCivil <ZouBCivil@discussions.microsoft.com> wrote: >The Excel Add-in that allows you to retriev...

Data from Access query to Excel
To pull data from an Access 2003 database, I have created the queries in Access, then import into Excel. The problem is that all the numbers that are pulled into Excel are text and need to convert them into numbers to run formulas on. I have converted a few sheets by hand, but, some have will over 50,000 rows. Is there a function to select all number colums (the colums are the same through out the sheets) and convert? Thanks There are instructions here for converting text to numbers: http://www.contextures.com/xlDataEntry03.html You can select all the columns, and only the num...

How to delete duplicate data
Hi, I am using excel to consolidate monthly room booking data. I have a date column and time column. May I know how to to delete those rows which contains duplicate data with same date stated in the date columnand and same time range in the time column? Thank you. Data>filter>advanced filter, unique records only and copy to another location -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com (Remove ^^ from email) "PL" <PL@discussions.microsoft.com> wrote in message news:F45DAE2B-252D-4E17-81D8-0F4C060BCB21@m...

Can you delete and empty an open email
Hi there Can you delete and empty an open email? I know I can delete and empty a closed email by holding down shift and then deleting, but have not been able to figure out how to delete and empty from an open email. Is this even possible? Thanks for your help. Kylie On Wed, 18 May 2005 20:01:03 -1000, Kylie B"" <KylieB@discussions.microsoft.com> wrote: > Hi there > > Can you delete and empty an open email? I know I can delete and empty a > closed email by holding down shift and then deleting, but have not been > able > to figure out how to delet...

Where can I get a template that will calculate profloss after buy.
I need a template that will calculate all the possible variables in a share trade. The rate on buying, every day entry (7 days),Profit/loss on holding, prof/loss on sale etc. ...

Contacts vs Personal Address Book. Can't pull emails into TO fiel
Howdy, I'm using Outlook 2k3 and having a slight problem. WHen I view my Contact list in the main view, I have two sets (not sure why but I'll be combining them). These lists have standard name, email, etc. Problem is when I go to create a new email message and click the TO: button, it tries to look for my Personal Address Book which I don't have. How can I make it not use the PAB and use the Contacts list instead? Thanks. OK, I think I just stumbled on the issue. For some reason on my COntacts Properties, the check box to tell Outlook to use that in the Address Book ...

Compressed Outlined Data Copy
I need to copy only the data that is compressed in Grouped rows and not all the rows when they are expanded. When I copy and paste the compressed data, I get all the data that is within the group, and not just the compressed data. For example: I need just the following (which is compressed): Ship $ Line $5,278.00 CYP Total $133,122.00 TI Total $34,660,135.00 Vic Total $34,798,535.00 Grand Total when I copy it (D2:E17, but only showing 5 lines because of the compression), it pastes 16 lines: Ship $ Line $123.00 CYP $44.00 CYP $4,444.00 CYP $667.00 CYP $5,278.00 CYP Total $123,124.00 TI $...

can't find form
i have a command button on a form [frmMainMenu] that when clicked, closes that form and opens another [frmFixtureSchedulePrintOptions]. The 2 share the same table as a record source, so i'm doing this to avoid conflicting updates the code behind [frmMainMenu] reads like this (below), and functions correctly DoCmd.Close stDocName = "frmFixtureSchedulePrintOptions" DoCmd.OpenForm stDocName, acNormal on the new form [frmFixtureSchedulePrintOptions], there is a command button to save to close itself, and open the previous from [frmMainMenu], and the ...

inverting data #2
Please help, I'm stuck! I conducted a survey where respondants each gave answers to 30 questions using a Likert scale i.e. they answered either 1,2,3,4 or 5 to each question. The data is in the following form: Question # q1 q2 q3 q4 q5 1 2 4 2 1 2 2 4 1 2 1 4 3 4 4 2 1 4 4 2 3 2 2 5 5 2 3 4 5 3 My problem is that half of the questions were asked in a negative direction to avoid possible response bias. For the answers to these questions, I need to reverse the answers. If the repondant answered 1, I want to record 5, if 2 then 4, if 3 then 3, if 4 then 2, if 5 then 1. Is there an...

Can't Make this Work
I create a new Datasheet form from tlPB and get the "FIRST SQL VIEW". I then select DirectoryID and from the Row Source, I get the "SECOND SQL VIEW". I then select DeptID and from the Row Source, I get the "THIRD SQL VIEW". DirectoryID and DeptID are both Combo Boxes. Problem I want only those DeptID's that are related to the DirectoryID that has been entered in the Datasheet. Can someone show me how this is done? -------------------------------------------------------- FIRST SQL VIEW SELECT tblPB.PBID, tblPB.DirectoryID, tblPB.DeptID FROM t...

Can I use oulook from home and have access at work?
When I set up my outlook I lose all my inbox messages online and would not be able to read my email from work. Is there a way to have my inbox available on outlook and remain online? Just to clarify, are you asking how to set up Outlook at home to receive email from work? If that's your question, you need to ask your IT Admin or Exchange Admin if you can use OWA and how that's done. That wouldn't have email coming into your Outlook account but it is a form of Outlook, Outlook Web Access. This may or may not be allowed. Your IT staff would of course have all kinds of securi...

Can't open My Money 2000
Hello all, Version 4.90.3, Windows ME I am not sure if this is the proper newsgroup for this program, and if not, I do apologize. I have My Money 2000 on my computer, it came with the original install when I bought my computer. For some reason, I have not been able to open the program since last Sunday. I only use this for my personal check registry, and have all my records and such backed up to a floppy and a separate folder. I went to the folder that has the My Money files and tried to reinstall it by using the Setup, but, it says 'Setup could not open the file: C:\APPLZIP\MONEY\MONE...