vba 比较两张工作表中的整行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26597310/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Compare entire rows in two sheets
提问by Sunny
I have two sheets with same data and I want to compare entire row in two sheets.
我有两张具有相同数据的工作表，我想比较两张工作表中的整行。
On Sheet1 (old data)
Col A | Col B
1001 | My Val 1
2001 | My Val 2
3001 | My Val 3
On Sheet2 (new data)
Col A | Col B | C
3001 | My Val 3 |True
1001 | My New Val 1 |False
2001 | My New Val 2 |False
回答by Richard Le Mesurier
Instead of trying to think in terms of comparing rows, make the problem simpler. It is easier to compare just a single cell - so first combine your "whole row" into a single cell. This is easy by concatenating all the cells using the &symbol.
不要试图从比较行的角度来思考，而是让问题更简单。仅比较单个单元格更容易 - 因此首先将您的“整行”合并为一个单元格。通过使用&符号连接所有单元格，这很容易。
Insert a new (hidden) column C on both sheets, that combines the other columns with a formula like:
在两个工作表上插入一个新的（隐藏的）列 C，它将其他列与一个公式组合在一起，例如：
= A1 & B1
= A1 & B1
Now you have a summary that is easy to compare, because you are just looking at single cells and a single column.
现在您有了一个易于比较的摘要，因为您只查看单个单元格和单个列。
On your new sheet, insert a new column D that uses VLOOKUPto see if the row exists on sheet 1:
在新工作表上，插入一个新列 D，用于VLOOKUP查看该行是否存在于工作表 1 上：
=VLOOKUP( C1, Sheet1!C:C, 1, false)
=VLOOKUP( C1, Sheet1!C:C, 1, false)
Now this will give you an error if the row is not found, and will return the row if it is found.
现在，如果未找到该行，这将给您一个错误，如果找到该行，它将返回该行。
Your new column E (which corresponds to your old column C) can be calculated with:
您的新列 E（对应于您的旧列 C）可以通过以下方式计算：
=NOT( ISERR( D1 ))
=NOT( ISERR( D1 ))
Hide unused columns as required.
根据需要隐藏未使用的列。
回答by InbetweenWeekends
I started down the path of the accepted answer, concatenating columns. But with 107 columns, this proved to be tedious.
我开始沿着接受的答案的路径，连接列。但是对于 107 个列，这被证明是乏味的。
My solution for this was to paste both sheets into one, keeping the columns aligned. Then, on the Data tab, click Remove Duplicates. If your remaining row count equals your starting row count, they're identical. If they're not, you'll need to filter columns to find the ones that are not the same.
我对此的解决方案是将两张纸粘贴到一张纸上，保持列对齐。然后，在数据选项卡上，单击删除重复项。如果您的剩余行数等于您的起始行数，则它们是相同的。如果不是，您需要过滤列以查找不相同的列。
