Announcement

Collapse
No announcement yet.

Compare Two Spreadsheets And Return Differences

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    Re: Compare Two Spreadsheets And Return Differences

    Let's see if this can get you started:

    Code:
    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

    Comment


    • #3
      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, 10:13.
      Bill
      Tip: To avoid chasing code always use Option Explicit.

      Comment


      • #4
        Re: Compare Two Spreadsheets And Return Differences

        Sorry,
        Forgot to mention code is in sheet2 module

        Bill
        Bill
        Tip: To avoid chasing code always use Option Explicit.

        Comment


        • #5
          Re: Compare Two Spreadsheets And Return Differences

          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
          Code:
          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

          Comment


          • #6
            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.

            Comment


            • #7
              Re: Compare Two Spreadsheets And Return Differences

              Monica,

              Try this instead:

              Code:
              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

              Comment


              • #8
                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!

                Comment


                • #9
                  Re: Compare Two Spreadsheets And Return Differences

                  Happy Friday!

                  Comment


                  • #10
                    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, 10:13.
                    Bill
                    Tip: To avoid chasing code always use Option Explicit.

                    Comment

                    Working...
                    X