Compare 2 Worksheets Create a 3rd depending on results

I have a unique problem that none of the other posts or shareware seems
to solve.

I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to
the "O"). The data is just numbers but the second column either has the
words: "new" or "cancel" which is important as you will see.  I need to
compare the two worksheets and create a third worksheet (update.xls)
depending on the three possible results:

1) If a row is removed in the new.xls file = copy the row from the
orig.xls file and make the 2nd column "Cancel"

2) If a row is added to the new.xls file = copy the row from the
new.xls file and make the 2nd column for this record "New"

3) If a row has changed in any way in the new.xls file = copy the row
from the new.xls file and make the 2nd column for this record "New".

I have limited VBA/Excel skills.  I wanted to make this relatively
simple but don't know where to begin!  Any help or response would be
appreciated.  
Kevin

0
kmckellar (2)
2/4/2005 8:41:54 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
561 Views

Similar Articles

[PageSpeed] 1

How about running this against a copy of the Original worksheet.  This macro
updates it in place.  If it doesn't work correctly, close without saving (or
delete the copy!).

I'm guessed that you had a key column (column A in my code) that contained a
unique key per worksheet (order number/site id/Social security number...).

this code uses that unique value in column A to look for matches in both
worksheets.  

Option Explicit
Sub testme()

    Dim OrigWks As Worksheet
    Dim NewWks As Worksheet
    
    Dim myCell As Range
    Dim DestCell As Range
    Dim res As Variant
    Dim iCol As Long
    
    Dim NewKeyRng As Range
    Dim OrigKeyRng As Range
   
    Set OrigWks = ActiveWorkbook.Worksheets("Orig")
    Set NewWks = ActiveWorkbook.Worksheets("New")
    
    With OrigWks
        Set OrigKeyRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
        'wipe out any existing comments in column B??
        OrigKeyRng.Offset(0, 1).ClearContents
    End With
    
    With NewWks
        Set NewKeyRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
    End With
    
    'look for deletions/changes first
    For Each myCell In OrigKeyRng.Cells
        res = Application.Match(myCell.Value, NewKeyRng, 0)
        If IsError(res) Then
            'In Orig, not New.  Mark it deleted
            myCell.Offset(0, 1).Value = "Cancel"
        Else
            'found keys in both spots
            For iCol = 3 To 15
                If myCell.Offset(0, iCol - 1).Value _
                    = NewKeyRng(res).Offset(0, iCol - 1).Value Then
                        'keep looking
                Else
                    NewKeyRng(res).Resize(1, 15).Copy _
                        Destination:=myCell
                    myCell.Offset(0, 1).Value = "Changed" ' why New???
                    Exit For
                End If
            Next iCol
        End If
    Next myCell
    
    'look for additions.
    For Each myCell In NewKeyRng.Cells
        res = Application.Match(myCell.Value, OrigKeyRng, 0)
        If IsError(res) Then
            'In new, not Orig.  copy it over
            With OrigWks
                Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
            End With
            
            myCell.Resize(1, 15).Copy _
                Destination:=DestCell
            
            DestCell.Offset(0, 1).Value = "New"
        End If
    Next myCell
    
End Sub

(I used Changed--instead of New.  You can change the code if you need to.  But I
thought that it was a more accurate description.)

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(Remember it updates the Orig worksheet!


Kevin wrote:
> 
> I have a unique problem that none of the other posts or shareware seems
> to solve.
> 
> I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to
> the "O"). The data is just numbers but the second column either has the
> words: "new" or "cancel" which is important as you will see.  I need to
> compare the two worksheets and create a third worksheet (update.xls)
> depending on the three possible results:
> 
> 1) If a row is removed in the new.xls file = copy the row from the
> orig.xls file and make the 2nd column "Cancel"
> 
> 2) If a row is added to the new.xls file = copy the row from the
> new.xls file and make the 2nd column for this record "New"
> 
> 3) If a row has changed in any way in the new.xls file = copy the row
> from the new.xls file and make the 2nd column for this record "New".
> 
> I have limited VBA/Excel skills.  I wanted to make this relatively
> simple but don't know where to begin!  Any help or response would be
> appreciated.
> Kevin

-- 

Dave Peterson
0
ec357201 (5290)
2/4/2005 11:49:03 PM
Reply:

Similar Artilces:

Prevent change to refs in second worksheet when drag cells in firs
I have a complex calendar in Excel. Each week, admins Ctrl-drag cells among different locations in the primary calendar worksheet. I have a second worksheet that needs to list those cell values in a fixed configuration, e.g. the value in Sheet1!A1 needs to always appear in the cell of Sheet2!G10. Sheet2!G10 contains the formula: “Sheet1!$A$1”. If the user drags Sheet1!A1 to Sheet1!A3, the formula in Sheet2!G10 gets automatically updated to “Sheet1!$A$3”. I have tried all combos of locking and protecting to prevent this updating. Is there a way to do this? Try referencing the other shee...

Publisher calendars #2
I'd like to know how to put the "Sunday" column at the far left This is determined by your regional settings in the control panel. A workaround, find a year where Sunday falls on your calendar start day and change the week headings. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "arriputter" <arriputter@discussions.microsoft.com> wrote in message news:AFA42774-09A1-4A61-AEC0-0E61C45B6975@microsoft.com... > I'd like to know how to put the "Sunday" column at the far left ...

compare and merge documents in 2007
How do you compare 2 excel documents with track changes and merge them together after accepting the changes in Excel 2007? ...

can I print selected cells only within a worksheet
I have designed a simple template using text only for a unique advice note. I want to be able to add variable information within this template but need only for the variable information to print onto a pre-printed advice note. I do not want to hide the template as I need to see exactly where to insert the variables but I do not want it to print. Anyone help? Thanks. Maybe you can use this ? http://www.rondebruin.nl/print.htm#Hide -- Regards Ron de Bruin http://www.rondebruin.nl "martin hanley" <martin hanley@discussions.microsoft.com> wrote in message news:AA062677-3...

How do I create a Cost Volume Profit Graph in Excel?
I was wondering if I could get some help on how to create a CVP graph in Excel 2007. A cost volume profit shows how costs, revenues, and profits vary with volume (sales). You can either plot total cost, or fixed and variable costs, which add up to total costs. One way to show this is to make a break-even chart. Put unit sales (number of items sold) in the first column, fixed costs in the second (which are a constant), variable costs in the third column (these are typically a straight line through zero), and in the fourth insert formulas that sum fixed and variable costs to make total ...

pop up calender PT 2
Thanks Jim This is what I needed but I am having a problem connect the date to a cell that will work with the VLOOKUP attached Can this date be tied to the cell? You can try the Microsoft Date and Time Picker control. Add the Control Toolbox menu to your toolbars. Select the last button that looks like a hammer and wrench. Select the Microsoft Date and Time Picker Control. Add that to your sheet. -- HTH... Jim Thomlinson "Norm" wrote: > Is there a pop up calender that can be embedded when sharing or emailing > worksheets? > Thanks Wa...

Outlook contacts #2
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C6D122.32A1B6F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hey Folks Anybody know of a third party product that will allow a network XP Pros = machines running Outlook to be able to share their contacts without = Exchange server hosting their emails? ie. they're using POP3 from a = third party mail provider? R Green ------=_NextPart_000_0008_01C6D122.32A1B6F0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printa...

basic question....create array
I searching the web to find how to create an array of any type from .net. I found this way: $nelement= 20 $array = new-object SomeType[] $nelement I know that $nelements is the number of elements in the array but.....when I read Get-help about_arrays I don't find any reference to that argument.......where is the documentation for that way of array creation? the only way that I found in the help is [SomType[]] $array = (element1,element2,...$elementN) but for that I need the elements already, and I don't have it..........I just need and empty array of some type. -- Mi...

Creating one trendline for multiple series
Hi, I have a document withfour different series and these are all plotted onto one curve. I want a trendline that fits all four series of data onto one trendline. I don't want to create a new series with all of the data and then fit a trendline to that. Is there any way I can just fit one to all four sets of data? Thanks > I don't want to create a new series with all of the data and then fit a > trendline to that. Is there any way I can just fit one to all four sets of > data? Unfortunately this is how it has to be done. - Jon ------- Jon Peltier, Microsoft Excel...

2 different types of data sets displayed on 1 graph
I have a district that I wish to show performance (sales)in the months from July to November and comapred this with 2004 and 2005. Now on top of this I want to display on the same graph a second variable say number of customers for each month and each year, in order to bring some greater relevance to the static bar graphs that I propose doing for the first part. So I have been assisted on this discussion group to have a bar graph that is set up with the months as rows and the 2004 and 2005 as columns for the sales data BUT how do I now display the second variable 'number of customer...

Creating a chart of overlaid area circles?
Can anyone help? I am trying to create a chart that contrasts size by laying 3 different sized circles on top of each other (so it will basically look like a shooting target). I know i can represent this data in other ways but this will have greatest impact for the purpose. Can Excel do this? And if so, how? - Please bear in mind I am not very techy so the simpler the better! Thanks very much Hi, You could use a bubble chart. That takes 3 parameters x,y and size. keep the x and y constant and adjust the size. Cheers Andy m.nutt1 wrote: > Can anyone help? > > I am trying to...

SAP Installation #2
Hi Just to share my experience , he is good service provider , please contact him on 1sapmate@gmail.com ...

Can publisher 2003 be installed on 2 computers?
I have a office computer and home computer. I will be purchasing Publisher 2003, but can I install them on both of my computers? campfig wrote: > I have a office computer and home computer. I will be purchasing > Publisher 2003, but can I install them on both of my computers? Technically, Publisher 2003 can be installed on a desktop and a mobile computer (ie laptop). When you go to activate your second computer it just depends on whether or not you tell them it's a laptop. -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com Windows Marketplace Moderator This...

Send mail problem #2
POsted this in outlook.general with no response so will try this group. APologies for the cross post but I am still unable to resolve this issue. W2K Pro will all patches and updates. Had Outlook running fine for a long time strictly for internet email at home. Worked on getting a VPN connection through our firewall at work. The VPN connection did work but I was unable to get my outlook to pull mail from our server once I reconfigured Outlook for Exchange. Uninstalled Outlook and reinstalled. Configured for my internet account only and now it will not send mail at all. The server properties...

RMS Hardware Configuration chat today, 2/10, at 11 a.m. PST
Hi all, I wanted let you know that we're getting ready to start a live chat in the MBS chat room at 11 AM PST -- today. Here are the details: Live Chat: Hardware configuration in Microsoft Retail Management System 1.2 Get answers to your questions on hardware setup and configuration in this February 10 chat room event hosted by Microsoft Retail Management System developers Balaji Balasubramanian, Josef Schauer, Dan Hanke, and Sylvester La Blanc. You can find our chat room here: http://www.microsoft.com/businesssolutions/chatroom.aspx Hope to see some of you there! Best, Michelle...

Inventory adjustment creates two journal entries
I was tracking the results of an inventory adjustment transaction when I discovered the system broke the resulting entry to the G/L into two, separate journal entries. The adjustment transaction (# 0000000009826) contained 123 lines; two journal entries were output by GP. The first entry had the following reference information: Journal Entry #: 499514 Source Document: IVADJ Audit Trail Code: GLTRX00021619 Reference: Transaction Entry Orig Audit Trail Code: IVADJ00001482 The second journal entry had the following information: Journal Entry #: 499515 Source Document: GJ Audit Trail Code...

Pivot Table Help #2
- Automaticly Refreshing Pivot Table Data I have a made a macro called "depsub" that gathers sales data from each one of our sales departments, compiles it onto one page, sorts it and SUBTOTALs the data by employee and department. I also have a PivotTable that needs to use this same data. PivotTables can not be generated from subtotaled data. So I have to nest a macro within my "depsub" macro that generates a PivotTable prior to the data being subtotaled. Problem is every time I try to do this the macro errors out and stops when I try to creat the pivot table. Here i...

creating text box dynamically in ms access
can any body please tell me the code to create text boxes dynamically or else please give me the link abt that information Thanq See help on CreateControl In general, this is only useful if you are creating some kind of wizard form that creates whole forms for end users. You don't want to be creating text boxes on the fly for a normal form. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "ravindar thati" <ravindarjobs@gmail.com> wrote in mes...

Using Dependents
In a particular instance when I am using the dependents search facility, a small symbol like a table or an Excel sheet appears, (the dependent arrow pointing to it). It's as if it is indicating that some other sheet has data dependednt on the cell. But I can't find any other sheet with dependent data. Double-click the arrow to the sheet symbol and Excel will display the dependent cells -- Kind regards, Niek Otten Microsoft MVP - Excel "Zembu" <Zembu@discussions.microsoft.com> wrote in message news:352631A7-0BF4-451D-8781-669C26270BF6@microsoft....

Worksheet hangs
My workbook freezes whenever I try to use Data>Subtotal directly or through a macro. Why does this happen. When I deleted ALL macros there was no problem. Any suggestions. If all my macros have to be deleted before using Data>Subtotal, is a code available and how can I prevent the file being saved after the macros have been deleted. Thank you in advance. RobertR. So sorry, even when the macros are disabled, the problem persists. > RobertR. Disregard my post. Seems to work now. Have to identify earlier cause myself. > RobertR. ...

comparing 2 sheets
hi! k, this is wht i have sheet1 (short list of about 400 parts with no descripiton) columns - part # description sheet2(complete list of about 2000 parts with description) same columns - part # description now is there a way that excel can compare a part # from sheet 1 to that of sheet 2 and then fill the corresponding description. if you can help will save me hours of boring work. thanks a lot!! Hi keyur in sheet 1, column B (description) enter the following in B2: =VLOOKUP(A2,'sheet 2'!$A$1:$B$9999,2, FALSE) copy this for all rows in sheet 1 HTH Frank keyur wrote...

creating contacts from Account
>From within an account, if I try to create a new contact by clicking on the Contact button>new contact, populate the required fields, in this case just the last name seems required (in Red), the first name recommended (in Blue), and hit save, I get the following: Server Error in '/' Application. -------------------------------------------------------------------------------- The parameter is incorrect. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it ori...

Date tracking on worksheets
A friend wrote with a question (below) about date tracking on individual worksheets within a workbook. I know a little bit of Excel, but this is way beyond me! Any ideas out there?! Thanks in advance....Rick! "is there a way to make each tab of a document, like for example one document 5 tabs, automatically date the tab each time u open it. she want to track the activity on each tab, not the document in its entirety and was wondering if that was possible?" I was thinking that maybe it needs to date it when you exit the worksheet, so that the next time you open it, it will show th...

Event ID 5896 after upgrade from 1.2 to 3.0
As the subject, I upgraded from 1.2 to 3.0, and now everytime a mail passes through I get the following error in the Application Event: Source: CrmExchangeQueueServ Event ID: 5896 Description: An HTTP Status of '404 - Not Found' occurred while attempting to deliver a message. Please check the URL in the registry. Message subject: 'subject of mail' URL: 'http://servername/MSCRMServices/CrmEmail.srf' Checked the location above, and sure enough - there is no CrmEmail.srf file, so it throws a 404 error.. great, only - why is this file missing?!! I presume it didn&#...

Adding Chinese holidays to Outlook 2002 Calendar #2
Hi, I've been all over the place, looking for a way to add Chinese holidays to the Outlook calendar. Already checked within Outlook and Chinese holidays are not on the list of countries available. Taiwan, which would probably be the same is also not available. I've been to Slipstick, as suggested by someone, but can't find any Chinese holiday files to add to the outlook.txt file. Anybody have any ideas? Melissa "playmissy4me@yahoo.com" wrote: > Hi, > > I've been all over the place, looking for a way to add Chinese holidays > to the Outlook cale...