+ Reply to Thread
Results 1 to 6 of 6

Comparing two sheets of data for changes.

  1. #1
    Registered User
    Join Date
    03-07-2006
    Posts
    18

    Comparing two sheets of data for changes.

    Hi everyone - heres a little puzzle thats beyond my capabilities.

    I want to compare two excel documents, I could possibly move the sheets I want to compare into the same workbook.

    Basically, I need to be able to see what changes have occured between the two sheets, firstly for new entries (new records/rows) OR modifications (alterations within a record/row). Each record (row) has the following 9 criteria (coloumns) along it, these are: Reference Number, Title, First Name, Surname, Age, Description, Start Date, End Date and Duration. Each Record/Row (is a client and) is assigned a Unique Reference Number.

    I am after a macro or VBScript code to compare the sheets and then generate a seperate list displaying these NEW and MODIFIED records.

    For example: Only showing or listing those records (rows) that are:
    NEW entries - eg. A record (row) that appears only on sheet 2 (new sheet) and NOT sheet 1 (old/original sheet).
    MODIFIED entries - eg. A record (row) that has altered one of its other criteria (not included reference number) like description, where on sheet 1 (old/original sheet) the description was 'dog', but on sheet 2 (new sheet) it is now 'cat'.

    Any advice or ideas on this would be much appreciated.

    Regards,

    PC

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    Excel has a compare and merge feature (on the tools menu). Have a look at that.

    Matt

  3. #3
    Registered User
    Join Date
    03-07-2006
    Posts
    18
    Quote Originally Posted by Mallycat
    Excel has a compare and merge feature (on the tools menu). Have a look at that.

    Matt
    Thank you for the suggestion, but could you be precise. What is the name of this function?

    I take it a VBScript would be out of the question here?

  4. #4
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    It's called Tools\compare and merge workbooks. I think VBA would be very hard.

    The other thing you could do is get a blank sheet, in cell A1 enter a formula like =sheet1!A1=sheet2!A1 If the 2 cells are the same, it will return true. If it returns false, they are different.

    Matt

  5. #5
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,075
    Quote Originally Posted by Mallycat
    It's called Tools\compare and merge workbooks.
    I've been watching this thread because this is a tool I could use regularly. In my Excel, though, the menu option is dim. Any suggestions, Matt?
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

  6. #6
    Registered User
    Join Date
    10-08-2006
    Posts
    14
    I have something which I use to track changes that (in mycase) takes place in sheet 4 due to changes in another workbook to which it is linked. The cells that have changed are highlighted in sheet 4 in red fillcolor. This may not tell you which is new and which is modified. you may want to delete the actions initiated on sheet 1 which is custom made for my case.

    See if this works for you after making changes as you need regarding the range to be observed for changes. You need to run Sheet 2 code first time to initialise. everytime you want to check for changes you will have to replace sheet 4 with the new/modified sheet and it will tell you which cells have changed.

    I am not an expert and hence this may not be the best solution.


    Madhusudanan




    CODE IN SHEET 2



    Public Sub setref()



    Dim ind As Integer
    Dim ind1 As Integer
    Dim art As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set art = Sheet4.Range("a1:u115")
    Set arr = Sheet3.Range("a1:u115")



    For ind = 1 To 115
    For ind1 = 1 To 21
    If art.Cells(ind, ind1) = Empty Then
    arr.Cells(ind, ind1) = Empty
    End If

    If arr.Cells(ind, ind1) <> art.Cells(ind, ind1) Then
    arr.Cells(ind, ind1) = art.Cells(ind, ind1)
    End If

    Next
    Next


    Application.EnableEvents = True
    Application.ScreenUpdating = True


    End Sub


    CODE IN SHEET 4


    Private Sub worksheet_calculate()

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Dim ind1 As Integer
    Dim ind As Integer

    Dim pref As String
    pref = MsgBox("reset highlights?", vbOKCancel)
    If pref = vbOK Then
    Module1.reset
    Else: GoTo 100
    End If

    Set arv = Sheet4.Range("a1:u115")
    Set arr = Sheet3.Range("a1:u115")
    Set arx = Sheet1.Range("a1:u115")

    For ind = 1 To 115
    For ind1 = 1 To 21
    If arr(ind, ind1) <> Empty Then

    If arr(ind, ind1) = arv(ind, ind1) Then

    If ind1 = 20 Then
    sh1reset ind, ind1
    End If

    ElseIf Abs(arr(ind, ind1) - arv(ind, ind1)) <= 0.0001 Then
    arx(ind, ind1) = Empty
    Else: Sheet4.Cells(ind, ind1).Interior.ColorIndex = 3

    If ind1 = 20 Then
    findchanges ind, ind1
    End If
    End If
    End If
    Next
    Next


    Sheet2.setref
    MsgBox ("Total variation in Amount = Rs." & (Sheet1.Cells(115, 20) \ 1))
    100: Application.EnableEvents = True
    Application.ScreenUpdating = True



    End Sub


    Sub findchanges(ByVal ind As Integer, ByVal ind1 As Integer)

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Sheet1.Cells(ind, ind1) = arv(ind, ind1) - arr(ind, ind1)
    Sheet1.Cells(ind, ind1).Interior.ColorIndex = 3


    End Sub

    Private Sub sh1reset(ByVal ind As Integer, ByVal ind1 As Integer)
    Application.EnableEvents = False
    Application.ScreenUpdating = False


    arx(ind, ind1) = Empty

    End Sub


    CODE IN MODULE 1



    Public arr As Range

    Public arv As Range

    Public arx As Range



    Sub reset()
    Dim ind As Integer
    Dim ind1 As Integer


    Application.EnableEvents = False
    Application.ScreenUpdating = False

    Set art = Sheet4.Range("a1:u115")

    For ind = 1 To 115
    For ind1 = 1 To 21
    If Sheet4.Cells(ind, ind1) <> Empty Then
    If Sheet4.Cells(ind, ind1).Interior.ColorIndex = 3 Then
    Sheet4.Cells(ind, ind1).Interior.ColorIndex = 2
    End If

    If ind1 = 20 Then
    If Sheet1.Cells(ind, ind1).Interior.ColorIndex = 3 Then
    Sheet1.Cells(ind, ind1).Interior.ColorIndex = 2
    End If

    Sheet1.Cells(ind, ind1) = Empty


    End If
    End If
    Next
    Next





    End Sub
    Last edited by rmadhusudanan; 11-24-2006 at 06:32 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1