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.


    I’m 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 I’ve 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 I’m open to suggestions. Item 3, I’m at a loss as how to do this. I’ve added an example (shaded in blue) to show what I’m 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 wouldn’t 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.

  • As a start you can speed up your macro by eliminating some double do loops. Foe example, you could replace the following snippet


    with something like the following



    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.

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