Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Compare Two Spreadsheets And Return Differences

  1. #1
    Join Date
    27th July 2006
    Posts
    3

    Compare Two Spreadsheets And Return Differences

    Hello,
    This is my first time posting. I have two spreadsheets that I have to compare three columns in each to each other and find the difference between them. And I'm not sure how to do this.

    I have attached a sample file to show what I'm looking at.

    Basicly I need to know the differences between each spreadsheet based on zips. Each zip is assigned to a store and group and the "data" spreadsheet is the master. I need to compare the "system" spreadsheet and have it show me what is different based on each zip.

    I hope this is clear.

    Thank you.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    11th August 2006
    Location
    Outside St. Louis, MO
    Posts
    621

    Re: Compare Two Spreadsheets And Return Differences

    Let's see if this can get you started:

    VB:
    Sub highlight_the_differences() 
        Dim rData As Range, cData As Range, rSystem As Range, cSystem As Range 
        Dim x As Integer 
        Set rData = Sheets("Data").Range("C2:" & Sheets("Data").Range("C2").End(xlDown).Address(False, False)) 
        Set rSystem = Sheets("System").Range("C2:" & Sheets("System").Range("C2").End(xlDown).Address(False, False)) 
        For Each cData In rData 
            For Each cSystem In rSystem 
                If cData.Value = cSystem.Value Then 
                    For x = -1 To -2 Step -1 
                        If cData.Offset(0, x).Value <> cSystem.Offset(0, x).Value Then 
                            cData.Offset(0, x).Font.Color = vbRed 
                        End If 
                    Next x 
                    Exit For 
                End If 
            Next cSystem 
        Next cData 
        Set cData = Nothing: Set cSystem = Nothing 
        Set rData = Nothing: Set rSystem = Nothing 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Compare Two Spreadsheets And Return Differences

    Monica,
    Welcome to ozgrid posting.

    I added two columns in sheet2 to record the differences between Data and System. See attached.
    Last edited by Bill Rockenbach; May 5th, 2009 at 11:13.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Compare Two Spreadsheets And Return Differences

    Sorry,
    Forgot to mention code is in sheet2 module

    Bill

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,570

    Re: Compare Two Spreadsheets And Return Differences

    Quote Originally Posted by Monica
    Hello,
    This is my first time posting. I have two spreadsheets that I have to compare three columns in each to each other and find the difference between them. And I'm not sure how to do this.

    I have attached a sample file to show what I'm looking at.

    Basicly I need to know the differences between each spreadsheet based on zips. Each zip is assigned to a store and group and the "data" spreadsheet is the master. I need to compare the "system" spreadsheet and have it show me what is different based on each zip.

    I hope this is clear.

    Thank you.
    Hi
    try
    VB:
    Sub test() 
        Dim a, w(), i As Long, ii As Integer 
        a = Sheets("Data").Range("a1").Resize(,3).Value 
        With CreateObject("Scripting.Dictionary") 
            For i = 2 To UBound(a,1) 
                If Not .exists(a(i,3)) Then .add a(i,3), Array(a(i,1), a(i,2)) 
            Next 
            Sheets("System").Columns("d:e").ClearContents 
            a = Sheets("System").Range("a1").CurrentRegion.Resize(,3).Value 
            For i = 2 To UBound(a,1) 
                If Not .exists(a(i,3)) Then 
                    Sheets("System").Cells(i,1).Resize(,3).Interior.Color = vbYellow 
                    Sheets("System").Cells(i,4).Value = "New" 
                Else 
                    w = .item(a(i,3)) 
                    For ii = 1 To 2 
                        If w(ii-1) <> a(i,ii) Then 
                            Sheets("System").Cells(i,1).Resize(,3).Interior.Color = vbRed 
                            Sheets("System").Cells(i,4).Resize(,2).Value = w 
                        End If 
                    Next 
                End If 
            Next 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    27th July 2006
    Posts
    3

    Re: Compare Two Spreadsheets And Return Differences

    Thank you so much!

    Mavyak - this worked great but I'm really looking for something more like what Bill sent where it breaks it out/shows me what the master has.

    Bill - This is PERFECT but I can't get it to run. I just tried changing some numbers to see if it would kick it out but when I run the macro is says "named argument not found". Is there something I need to be doing everytime to run it?

    Thanks again! If I can get this working it will save me hours of pulling out my hair as my spreadsheets are 1,000+ rows.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    11th August 2006
    Location
    Outside St. Louis, MO
    Posts
    621

    Re: Compare Two Spreadsheets And Return Differences

    Monica,

    Try this instead:

    VB:
    Sub highlight_the_differences() 
        Dim rData As Range, cData As Range, rSystem As Range, cSystem As Range 
        Dim x As Integer 
        Sheets("Data").Range("D1").Value = "System Group" 
        Sheets("Data").Range("E1").Value = "System Store" 
        Sheets("Data").Columns.AutoFit 
        Set rData = Sheets("Data").Range("C2:" & Sheets("Data").Range("C2").End(xlDown).Address(False, False)) 
        Set rSystem = Sheets("System").Range("C2:" & Sheets("System").Range("C2").End(xlDown).Address(False, False)) 
        For Each cData In rData 
            For Each cSystem In rSystem 
                If cData.Value = cSystem.Value Then 
                    For x = -1 To -2 Step -1 
                        If cData.Offset(0, x).Value <> cSystem.Offset(0, x).Value Then 
                            cData.Offset(0, x + 3).Value = cSystem.Offset(0, x).Value 
                        End If 
                    Next x 
                    Exit For 
                End If 
            Next cSystem 
        Next cData 
        Set cData = Nothing: Set cSystem = Nothing 
        Set rData = Nothing: Set rSystem = Nothing 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    27th July 2006
    Posts
    3

    Re: Compare Two Spreadsheets And Return Differences

    Mavyak - THIS IS IT!!!!!!! It's perfect!

    I wish I found this forum months ago.....

    Again thank you so much for all your help!

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    11th August 2006
    Location
    Outside St. Louis, MO
    Posts
    621

    Re: Compare Two Spreadsheets And Return Differences

    Happy Friday!

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th July 2004
    Location
    Texas, USA
    Posts
    1,939

    Re: Compare Two Spreadsheets And Return Differences

    Monica,

    I think I found the problem and added a line of code to andle zip codes not found in Data. See attached.

    Glad mavyak's code works for you.

    Bill
    Last edited by Bill Rockenbach; May 5th, 2009 at 11:13.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Compare Two Worksheets And Display Differences
    By Shahidha in forum EXCEL HELP
    Replies: 4
    Last Post: May 9th, 2008, 13:54
  2. Compare 2 Ranges For Differences
    By thampw in forum EXCEL HELP
    Replies: 1
    Last Post: November 28th, 2007, 09:51
  3. Compare Rows For Differences
    By Ceriga in forum EXCEL HELP
    Replies: 5
    Last Post: June 2nd, 2007, 12:53
  4. Compare & Show Differences
    By deviltronics in forum EXCEL HELP
    Replies: 6
    Last Post: January 9th, 2007, 09:37
  5. Compare sheets and remark on differences
    By jolivanes in forum EXCEL HELP
    Replies: 2
    Last Post: February 13th, 2006, 05:55

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
  •  
porno