Compare rows in two different sheets

Question

I have two worksheets to compare. Sometimes a whole row of data may be added, deleted or changed. I need to compare this to the other worksheet.

For example, if one worksheet looks like:

(Sheet 1)
Max J 89231 
Sam L 82313
Penny H 23456
Mary K 91423

And the next Sheet looks like:

(Sheet 2)
Sam L 82313    (Max J Removed)
John S 71234   (Penny H changed to John S)
Mary K 91423
Thomas N 18123 (Thomas N added)

How do I display the differences in rows for the two sheets, in e.g. sheet3 using a macro?

(Sheet 3)
Max J 89231 
John S 71234  
Thomas N 18123

What is the layout of the sheets? In particular -- is it enough to look in the first column to see if a given row has been added or deleted? Also -- is the relative order of rows that are common to both sheets the same in both sheets, or can rows be permuted in addition to being added or deleted? — John Coleman

waqasahmed waqasahmed · Accepted Answer · 2016-01-03T23:25:41

Have two loops in each sheet - that check Sheet1 against Sheet2, and insert differences in Sheet3, and then Sheet 2 against Sheet1, and insert differences in Sheet3 also.

In half pseudo half vba code , something like this for Sheet1:

for i = beginning row in Sheet 1 to last Row in Sheet1
   'matchFound variable concludes if match is found. Initially no match
   matchFound=false
   continue=true ' boolean to tell us whether its worth proceeding if we have identified a match
   if (continue) then
       for j = beginning row in Sheet 2 to last Row in Sheet2
            'if Sheet1 row has been matched in a row in Sheet2 
            if Sheet1.Cells(i,1).Value = Sheet2.Cells(j,1).Value and
                 Sheet1.Cells(i,2).Value = Sheet2.Cells(j,2).Value then
                  matchFound = true
                  continue=false
            endif
        next j
    endif

    'no match found
    if matchFound = false then
           sheet3.Range(last used row + 1, 1).Value = Sheet1.Cells(i,1).Value
           sheet3.Range(last used row + 1, 2).Value = Sheet1.Cells(i,2).Value

    endif

next i

Use the above logic also for sheet2 but which checks those in sheet2 and not in Sheet1, and adds to Sheet3 - so sheet 2 is the outer for loop this time.

Compare rows in two different sheets

1 Answers