Announcement

Collapse
No announcement yet.

VBA: Compare Help

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

  • VBA: Compare Help



    Need help running compare between two worksheets.

    1. 1st sheet is my new data.
    2. 2nd sheet is my old data.
    3. 3rd sheet is where I want to show the differences.

    Im looking to do the following:
    1. Compare Column A from the old data (sheet 2) to the new data (sheet 1). If a new value for Column A appears in sheet 1 and is not on sheet 2, then I want the row copied from sheet 1 to sheet 3 and be appended by ADD in Column M.
    2. Compare Column A from the new data (sheet 1) to the old data (sheet 2). If an old value for Column A appears in sheet 2 and is not on sheet 1, then I want the row copied from sheet 2 to sheet 3 and be appended by DELETE in Column M.
    3. Compare Column A from the old data (sheet 2) to the new data (sheet 1). If the value for Column A is the same but the value for either Column B or Column L is different then I want the row copied from sheet 2 to sheet 3 and be appended by FROM in Column M and I want the row copied from sheet 1 to sheet 3 and be appended by TO in Column M.

    In my attachment Ive accomplished items 1 and 2, however, on large files it is ever so slow to do the comparisons. If anyone can suggest a better way Im open to suggestions. Item 3, Im at a loss as how to do this. Ive added an example (shaded in blue) to show what Im looking to obtain.
    Sheet 3 is broken up into 3 sections ADD, DELETE and FROM/TO. If this can be done by alpha/numerical sort in Column A then I wouldnt mind the sort not being in 3 sections, however the FROM/TO rows must be together based on Column A'

    Thanks for any suggestions that you may come up with. A novice trying to improve his Excel VBA skills.

  • #2
    As a start you can speed up your macro by eliminating some double do loops. Foe example, you could replace the following snippet
    Code:
    For i = 1 To new_REFDES_length
            Match = 0
            For j = 1 To old_REFDES_length
                If Worksheets(1).Cells(i, 1).Value = Worksheets(2).Cells(j, 1).Value Then
                    Match = 1
                End If
            Next j
            If Match = 0 Then
                Cells(AddStartRow, 1) = Worksheets(1).Cells(i, 1)
                Cells(AddStartRow, 2) = Worksheets(1).Cells(i, 2)
                Cells(AddStartRow, 3) = Worksheets(1).Cells(i, 3)
                Cells(AddStartRow, 4) = Worksheets(1).Cells(i, 4)
                Cells(AddStartRow, 5) = Worksheets(1).Cells(i, 5)
                Cells(AddStartRow, 6) = Worksheets(1).Cells(i, 6)
                Cells(AddStartRow, 7) = Worksheets(1).Cells(i, 7)
                Cells(AddStartRow, 8) = Worksheets(1).Cells(i, 8)
                Cells(AddStartRow, 9) = Worksheets(1).Cells(i, 9)
                Cells(AddStartRow, 10) = Worksheets(1).Cells(i, 10)
                Cells(AddStartRow, 11) = Worksheets(1).Cells(i, 11)
                Cells(AddStartRow, 12) = Worksheets(1).Cells(i, 12)
                Cells(AddStartRow, 13).Value = "ADD"
                AddStartRow = AddStartRow + 1
            End If
        Next i
    with something like the following

    Code:
    For i = 1 To new_REFDES_length
            findit = Application.Match(Worksheets(1).Cells(i, 1).Value, _
                Worksheets(2).Range("A1:A" & old_REFDES_length), 0)
            If IsError(findit) Then 'no match
                Range(Cells(AddStartRow, 1), Cells(AddStartRow, 12)) = Worksheets(1).Range("A" & i & ":L" & i)
                Cells(AddStartRow, 13).Value = "ADD"
                AddStartRow = AddStartRow + 1
            Else
                'findit has row of match: process or store for later
            End If
        Next i
    At this point as noted you also have the location of the potential match so you could do part 3 with it. It would also speed up the macro to read all of your sheet1 data into a variant array, and all of the sheet 2 data into a variant array and then do the comparisons etc from them.

    for example.
    NewData=Worksheets(1).range("A1:L" & old_REFDES_length)
    from then on just use NewData(i,j) vice Worksheets(1).Cells(i,j) and Excel can process it faster.

    This doesn't answer your questions, but it
    may spped up your exploration.
    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

    Comment


    • #3
      Thanks Derk that was a big help. I've attached a new file showing the code. Runs really fast. My problem is still the 3rd request. Rather than key on differences in col B and col L when col A is equal on both sheets, is it any easier to look for differences in columns B thru L when col A is equal on both sheets? Any code pointing in that direction would be greatly appreciated.

      Comment


      • #4
        See if this does it. I added code to remember the matches on A that differed on B or L, and then processed them later.
        Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us will be ignored.

        Comment


        • #5
          Derk, I don't know how to thank you enough. Reworked the macro a little and it works great. Attached a new copy for anyone who wants to see it.
          Many, many thanks. What a great site this is.

          Comment


          • #6
            Re: VBA: Compare Help

            braveheart,

            Could you please share the referred attachment. I have a similar requirement in my current project.

            Thanks.

            Comment


            • #7
              Re: VBA: Compare Help

              Could you please share the referred attachment.

              Comment


              • #8


                Re: VBA: Compare Help

                This is an old post and the attachment was possibly lost in a Forum update. Try asking your own question in a new Thread, refer to this one.
                Hope that Helps

                Roy

                New users should read the Forum Rules before posting

                For free Excel tools & articles visit my web site

                RoyUK's Web Site

                royUK's Database Form

                Where to paste code from the Forum

                About me.

                Comment

                Working...
                X