比较两个不同工作表中的行

我有两个工作表要比较 . 有时可能会添加,删除或更改整行数据 . 我需要将其与其他工作表进行比较 .

例如,如果一个工作表看起来像:

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

接下来的工作表看起来像:

(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)

如何显示两张纸的行差异,例如: sheet3使用宏?

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

回答(1)

2 years ago

在每个工作表中有两个循环 - 检查Sheet1对Sheet2,并在Sheet3中插入差异,然后在Sheet1中插入Sheet1,并在Sheet3中插入差异 .

在半伪半vba代码中,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

对于sheet2也使用上面的逻辑,但是它检查了sheet2中的那些而不是Sheet1中的那些,并添加到Sheet3 - 所以这次表2是外部的for循环 .