根据列数据加入excel记录 [英] Joining excel records based on column data

问题描述

我有3个excel电子表格具有相同的列和（据称）相同的数据。我需要排列所有3个文档，并查找数据中的不一致。数据是数据中心的服务器信息，我希望服务器位置（行/ cab）和资产标签水平匹配，以便轻松查看3个文档之间的差异。如果一个文档丢失了服务器（没有匹配的资产标签或位置是空的）那么我想要一个空行插入其他2个文件，表明它不存在。希望这一切都是有道理的。

这是所有3张图片放在一起的图像。 （忽略颜色）

Excel图片http://img833.imageshack.us/img833/2937/unledcco.png

感谢您的帮助。

解决方案

这是一个基于ADO的解决方案，使用Excel和VBA。如果您需要有关使用ADO与Excel / VBA的更多信息，请尝试 http://support.microsoft.com/kb / 257819

我将假设您的所有三个电子表格都在一个工作簿中，它们被命名为Sheet1，Sheet2和Sheet3。显然要根据需要调整代码。

要使用ADO，请转到Visual Basic编辑器（通过菜单或通过 Alt - F11 ），然后通过工具>参考（或其后续版本中的等效项）添加引用：Microsoft ActiveX数据对象2.8库

我正在使用Excel 2003和更早版本的提供程序和连接字符串，因为这是我的版本。对于Excel 2007及更高版本，请使用（替换当然的文件名）：

Provider = Microsoft.ACE.OLEDB.12.0

数据源= filename;扩展属性= Excel 12.0 Xml;

（注意，连接字符串中常常会看到HDR = Yes，但这是Excel的默认设置，因此可以省略）

首先，我们将从原始的三个电子表格中创建资产标签的主列表。创建一个空白表并调用Sheet4，以便我们在某处放置列表。

在正常模块中，添加以下内容，然后运行它：

  Sub master_list（）
 
 Dim cn As ADODB.Connection 
 Set cn = New ADODB.Connection 
 
带有cn 
 .Provider =Microsoft.Jet.OLEDB.4.0
 .ConnectionString =Data Source =& ThisWorkbook.FullName& ; &安培; _ 
扩展属性= Excel 8.0; 
 .Open 
结束
 
 Dim rs As ADODB.Recordset 
设置rs =新建ADODB.Recordset 
 
 rs.OpenSELECT [资产标签] FROM [Sheet1 $] UNION SELECT [资产标签] FROM [Sheet2 $] UNION SELECT [资产标签] FROM [Sheet3 $];，cn 
 
使用工作表（Sheet4） 
 .Cells（1，1）.Value =Master
 .Cells（2，1）.CopyFromRecordset rs 
 End with 
 
 rs.Close 
 cn.Close 
 
 End Sub 
  

UNION中的UNION 运算符仅返回不同的记录，因此此查询给了我们所有三个电子表格中没有重复项的资产标签的完整列表。我使用主作为列名，以防止稍后的任何歧义

现在我们需要将三个单独的表格中的数据与主列表组合。创建一个新的空白表并调用Sheet5。现在添加并运行以下内容：

  Sub compare_sheets（）
 
 Dim cn As ADODB.Connection 
设置cn =新建ADODB.Connection 
 
带有cn 
 .Provider =Microsoft.Jet.OLEDB.4.0
 .ConnectionString =Data Source =& ThisWorkbook.FullName& ; &安培; _ 
扩展属性= Excel 8.0; 
 .Open 
结束
 
 Dim rs As ADODB.Recordset 
设置rs =新建ADODB.Recordset 
 
 rs.OpenSELECT * FROM（（[Sheet4 $] LEFT JOIN [Sheet1 $] ON [Sheet4 $]。[Master] = [Sheet1 $]。[Asset Tag]）& _ 
LEFT JOIN [Sheet2 $] ON [Sheet4 $]。[Master] = [Sheet2 $]。[Asset Tag]）& _ 
LEFT JOIN [Sheet3 $] ON [Sheet4 $]。[Master] = [Sheet3 $]。[Asset Tag];，cn 
 
 Dim fld As ADODB.Field 
 Dim i As Integer 
i = 0 
与工作表（Sheet5）
对于每个fld在rs.Fields 
i = i + 1 
 .Cells 1，i）.Value = fld.Name 
下一个fld 
 
 .Cells（2，1）.CopyFromRecordset rs 
 End with 
 
 rs。关闭
 cn.Close 
 
 End Sub 
  

为您提供与资产标签主列表相对应的所有三个电子表格的数据。列名可能会有点奇怪（例如Sheet1 $ .Asset标签等），所有的格式将丢失，但至少您可以看到哪些工作表缺少数据

I have 3 excel spreadsheets with the same columns and (supposedly) the same data. I need to line up all 3 documents and look for inconsistencies within the data. The data is server information at a data center and I would like the server location(row/cab) and Asset Tags to match horizontally for an easy view of differences between the 3 documents. If a document would be missing the server (No matching asset tag or location is empty) then I would want a blank line inserted in the other 2 documents showing that it is not present. Hopefully that all makes sense.

Here is the image of all 3 sheets put together. (ignore the colors)

Excel Image http://img833.imageshack.us/img833/2937/unledcco.png

Thanks for the help.

解决方案

Here's an ADO based solution using Excel and VBA. If you need more info about using ADO with Excel/VBA then try http://support.microsoft.com/kb/257819

I'm going to assume that all three of your spreadsheets are in a single workbook and that they are named Sheet1, Sheet2 and Sheet3. Obviously adjust the code as necessary.

To use ADO, go to the Visual Basic Editor (via the menus or via Alt-F11) and then via Tools > References (or its equivalent in later versions) add a reference to: "Microsoft ActiveX Data Objects 2.8 Library"

I'm using the provider and connection string for Excel 2003 and earlier because that's the version I have. For Excel 2007 and later, use these instead (replacing the filename of course):

Provider=Microsoft.ACE.OLEDB.12.0

Data Source=filename;Extended Properties=Excel 12.0 Xml;

(NB you'll often see "HDR=Yes" in connection strings but this is the default setting for Excel and thus can be omitted)

Firstly we'll create a master list of asset tags from the original three spreadsheets. Create a blank sheet and call it Sheet4 so that we have somewhere to put the list.

In a normal module, add the following then run it:

Sub master_list()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT [Asset Tag] FROM [Sheet1$] UNION SELECT [Asset Tag] FROM [Sheet2$] UNION SELECT [Asset Tag] FROM [Sheet3$];", cn

With Worksheets("Sheet4")
    .Cells(1, 1).Value = "Master"
    .Cells(2, 1).CopyFromRecordset rs
End With

rs.Close
cn.Close

End Sub

The UNION operator in SQL only returns distinct records so this query has given us a complete list of the asset tags in all three spreadsheets with no duplicates. I've used "Master" as the column name to prevent any ambiguity later on

Now we need to combine the data from the three individual sheets with the master list. Create a new blank sheet and call that Sheet5. Now add and run the following:

Sub compare_sheets()

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=Excel 8.0;"
    .Open
End With

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "SELECT * FROM (([Sheet4$] LEFT JOIN [Sheet1$] ON [Sheet4$].[Master] = [Sheet1$].[Asset Tag]) " & _
    "LEFT JOIN [Sheet2$] ON [Sheet4$].[Master] = [Sheet2$].[Asset Tag]) " & _
    "LEFT JOIN [Sheet3$] ON [Sheet4$].[Master] = [Sheet3$].[Asset Tag];", cn

Dim fld As ADODB.Field
Dim i As Integer
i = 0
With Worksheets("Sheet5")
    For Each fld In rs.Fields
        i = i + 1
        .Cells(1, i).Value = fld.Name
    Next fld

    .Cells(2, 1).CopyFromRecordset rs
End With

rs.Close
cn.Close

End Sub

That should hopefully give you the data from all three spreadsheets presented against a master list of asset tags. The column names will probably be a bit odd-looking (e.g. "Sheet1$.Asset Tag" etc) and all of the formatting will be lost but at least you can see which sheets are missing data

这篇关于根据列数据加入excel记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！