Announcement

Collapse
No announcement yet.

VBA: Compare Help

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

  • braveheart
    started a topic VBA: Compare Help

    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.

  • royUK
    replied
    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.

    Leave a comment:


  • ybroker
    replied
    Re: VBA: Compare Help

    Could you please share the referred attachment.

    Leave a comment:


  • sivarak
    replied
    Re: VBA: Compare Help

    braveheart,

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

    Thanks.

    Leave a comment:


  • braveheart
    replied
    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.

    Leave a comment:


  • Derk
    replied
    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.

    Leave a comment:


  • braveheart
    replied
    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.

    Leave a comment:


  • Derk
    replied
    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.

    Leave a comment:

Working...
X