通过将其与另一个工作表进行比较来更新工作表 [英] Update Worksheet by comparing it to another Worksheet

问题描述

我有一个excel工作表（Sheet1），我需要与另一个工作表（Sheet2）进行比较。

两个工作表的格式都完全相同。 （即列相同，标题相同）

当将Sheet1与Sheet2进行比较时，我需要检查现有记录的更新。

还检查Sheet2中不存在的Sheet1中的新记录，并将其附加到Sheet1的底部。

表2完全为空，不需要检查。

第2列将是密钥

<另请记住，每个工作表中有超过7000行。

更新＃1：

使用字典对象，我想出了这个。但是，似乎没有找到任何新条目。我做错了什么？

`
Sub createDictionary（）
Dim dict As Object
Set dict = CreateObject（Scripting。字典）

  Dim maxRows1，maxRows2 As Long 
 Dim i，j As Integer 
 Dim SheetOne， SheetTwo As Worksheet 
 
 maxRows1 = Sheets（Sheet1）。UsedRange.Rows.Count 
 
设置SheetOne = Sheet1 
设置SheetTwo = Sheet2 
 
对于i = 2 To maxRows1 
 
如果不是dict.exists（SheetOne.Cells（i，2）.Value ++ SheetOne.Cells（i，11）.Value）Then 
 dict.Add CStr（SheetOne.Cells（i，2）.Value）++ SheetOne.Cells（i，11）.Value，i 
 End If 
 
下一页i 
 
 maxRows2 = Sheets（Sheet2）。UsedRange.Rows.Count 
 
对于j = 2 To maxRows2 
 
如果不是dict.exists （Sheet2.Cells（j，2）.Value）Then 
 SheetTwo.Range（A& j&;&Z& j）.Copy 
 SheetOne.Range （A& maxRows1 + 1）。插入Shift：= xlDown 
 SheetOne.Rang e（A& maxRows1 + 1）.Interior.Color = RGB（200,200,200）
 End If 
 
下一个j 
 
设置dict = Nothing 
  

End Sub
`

解决方案

我经常在这个论坛:)重复一遍，但是，这样的操作使用SQL更容易处理。

我将使用Microsof Query （Excel数据 - >获取外部数据 - >从其他来源 - >从Microsoft查询）或者我建议使用我的SQL加载项到Excel： http://blog.tkacprow.pl/?page_id=130

似乎需要使用JOIN运算符来查找表1和表2之间的更改。然后使用UNION运算符，您可以使用LEFT OUTER JOIN加入第二个SELECT以添加其他新行。

I have an excel Worksheet ("Sheet1") that I need to compare with another Worksheet ("Sheet2").

Both Worksheets are formatted exactly alike. (ie columns are the same, with the same headers)

When comparing Sheet1 with Sheet2, I need to check for updates to existing records.

Also check for new records in Sheet2 that don't exist in Sheet1, and append them to the bottom of Sheet1.

Some Columns in Sheet 2 are completely blank and don't need to be checked.

Column 2 would be the "Key"

Also keep in mind that there are over 7000 rows in each worksheet.

Update #1:

Using the dictionary object, I came up with this. However, it doesn't seem to find any new entries. Am I doing something wrong?

` Sub createDictionary() Dim dict As Object Set dict = CreateObject("Scripting.Dictionary")

Dim maxRows1, maxRows2 As Long
Dim i, j As Integer
Dim SheetOne, SheetTwo As Worksheet

maxRows1 = Sheets("Sheet1").UsedRange.Rows.Count

Set SheetOne = Sheet1
Set SheetTwo = Sheet2

For i = 2 To maxRows1

    If Not dict.exists(SheetOne.Cells(i, 2).Value + " " + SheetOne.Cells(i, 11).Value) Then
        dict.Add CStr(SheetOne.Cells(i, 2).Value) + " " + SheetOne.Cells(i, 11).Value, i
    End If

Next i

maxRows2 = Sheets("Sheet2").UsedRange.Rows.Count

For j = 2 To maxRows2

    If Not dict.exists(Sheet2.Cells(j, 2).Value) Then
        SheetTwo.Range("A" & j & ":" & "Z" & j).Copy
        SheetOne.Range("A" & maxRows1 + 1).Insert Shift:=xlDown
        SheetOne.Range("A" & maxRows1 + 1).Interior.Color = RGB(200, 200, 200)
    End If

Next j

Set dict = Nothing

End Sub `

解决方案

I repeat this often across this forum :), however, such operations are much more easy to handle using SQL.

I would either use Microsof Query (Excel Data->Get External Data->From Other sources->From Microsoft Query) OR I suggest using my SQL Add-In to Excel: http://blog.tkacprow.pl/?page_id=130

Seems like you need to need to use the JOIN operator to find the changes between Sheets 1 and 2. Then using a UNION operator you join a second SELECT with a LEFT OUTER JOIN to add the additional new rows.

这篇关于通过将其与另一个工作表进行比较来更新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！