比较两个excel使用VBA的表 [英] Comparing two excel Sheets using VBA

问题描述

我有两个工作表A栏的地址清单。 sheet1和sheet2

我只是想知道是否有一种有效的方法来比较两个表中的数据，并执行以下操作：

从sheet1中删除不在sheet2中的地址。

将不在sheet1中的sheet2的地址添加到sheet1的末尾。

我的初衷是循环，但这显然是不高效的内存明智，因为在每张表大约10000个地址。

解决方案

是 - Microsoft Query（Excel中的SQL）。
$ b

您可以从 Data->从其他来源 - > Microsoft Query访问它

在比较/合并/更新多个数据源时，SQL非常出色。请参阅我的这篇文章部分。 Microsoft Query也会比任何VBA宏快得多。

您的问题的一个例子：

将第一个工作表减少到工作表中的项目

  SELECT * FROM [Sheet1 $] AS S1 INNER JOIN [Sheet2 $] AS S2 on S1.Address = S2.Address 
  

选择新地址

  SELECT S2.Address FROM [Sheet2 $] AS S2 LEFT JOIN [Sheet1 $] AS S1 on S1.Address = S2.Address 
 WHERE IsNull（S1.Address）
  

/ strong>

  SELECT * FROM [Sheet1 $] AS S1 INNER JOIN [Sheet2 $] AS S2 on S1.Address = S2 .Address 
 UNION ALL 
 SELECT S2.Address FROM [Sheet2 $] AS S2 LEFT JOIN [Sheet1 $] AS S1 on S1.Address = S2.Address 
 WHERE IsNull（S1.Address） 
  

I have a list of addresses in column A of both sheets. sheet1 and sheet2

I'm just wondering is there an efficient way to compare data in two sheets and do the following;

Remove addresses from sheet1 that are not in sheet2.

Add the addresses from sheet2 that are not in sheet1 to the end of sheet1.

My initial intention was to loop but this is apparently not efficient memory wise due to the being roughly 10000 addresses on each sheet.

解决方案

Yes there is - Microsoft Query (SQL in Excel).

You can access it from Data->From Other Sources->Microsoft Query

SQL is exceptional in comparing/consolidating/updating multiple data sources. See this section of my blog post here. Microsoft Query will also be much faster than any VBA macro.

An example of your issue:

Reduce first Worksheet to items that are in both Worksheets

SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address

Select new addresses

SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)

Join both

SELECT * FROM [Sheet1$] AS S1 INNER JOIN [Sheet2$] AS S2 on S1.Address=S2.Address
UNION ALL
SELECT S2.Address FROM [Sheet2$] AS S2 LEFT JOIN [Sheet1$] AS S1 on S1.Address=S2.Address
WHERE IsNull(S1.Address)

这篇关于比较两个excel使用VBA的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！