查找两个工作簿之间的匹配项

Question

我有两本数据工作簿。一个数据集是被拒绝的订单，另一个数据集是当前订单。我想知道我是否可以匹配订单，以便我可以利用拒绝文件中的订单。这样我就不必制作当前订单，同时可以减少被客户拒绝的订单堆。这是我的拒绝订单和当前/打印订单的数据表。 当前/已打印订单 这是被拒绝订单的数据表。 拒绝订单

我需要在三件事上匹配订单。首先，设计名称需要匹配，产品名称需要匹配，尺寸需要匹配，以获得“订单匹配”。

如何使用 Excel VBA 查找匹配项并在当前订单工作簿中创建一个新的 Excel 工作表，该工作表可以显示两个数据集之间匹配的订单。最终的数据输出将是订单号与两个文件中的订单号的对比。

我刚刚开始学习 vba，但这是一个我无法解决的复杂问题。请帮忙。我写了一段代码，但它无法运行。它说对象未定义。我写的代码是：

Sub Comparetwosheets()

Dim ws1 As Worksheet, ws2 As Worksheet Dim ws1row As Long、ws2row As Long、w1scol As Integer、ws2col As Integer 昏暗的报告作为工作表 Dim row As Long，col As Integer 调暗 R1 作为范围 Set R1 = Union(col(5), col(7), col(10))

Set reports = Worksheet.Add

'Set numrows = 数据行数

NumRows = Range("A1", Range("A1"). End(xlDown)).Rows.Count ' 选择单元格 a1。 范围(“A1”).选择 ' 建立“For”循环以循环“numrows”次。 对于 x = 1 到 NumRows x = 2 当 x < 时执行NonBlank

   x = x + 1
   
  Do While (ws1.R1 = ws2.R1)
  
  If ws1.rw2 = ws2.rw2 Then
  report.Cells(1, 1).Value = "Match"
  
  Else: x = x + 1
  
  Loop
  
  Loop
 

'选择活动单元格下 1 行的单元格。 ActiveCell.Offset(1, 0).选择

结束子

Answer 1

这应该可以为你做到。您可以将 CompareWorksheet 子例程中的以下变量设置为您需要的值（dataSet1、dataSet2、colPos1、colPos2、rowStart1、rowStart2）。我正在使用世界银行的随机数据集。

Sub CompareWorksheet()
    Dim dataSet1, dataSet2 As Variant
    Dim workbook1, workbook2 As String
    Dim worksheet1, worksheet2 As String
    Dim rowStart1, rowStart2 As Integer
    
    'Get the data into the dataSet variable using a function that goes through each workbook/sheet
    workbook1 = "dashboard-data-latest1.xlsx"
    worksheet1 = "2. Harmonized Indicators"
    dataSet1 = SheetToDataSet(workbook1, worksheet1)
    
    'Get the data into the dataSet variable using a function that goes through each workbook/sheet
    workbook2 = "dashboard-data-latest.xlsx"
    worksheet2 = "2. Harmonized Indicators"
    dataSet2 = SheetToDataSet(workbook2, worksheet2)
    
    'Set this do what columns you are interested in comparing
    colPos1 = Array(1, 2, 3)
    colPos2 = Array(1, 2, 3)
    
    'Set for where you want to start 1 would be row 1/now Header.
    rowStart1 = 2
    rowStart2 = 2
    
    'Compares the dataSets
    Compare2Sheets dataSet1, dataSet2, colPos1, colPos2, rowStart1, rowStart2


End Sub

Function Compare2Sheets(dataSet1 As Variant, dataSet2 As Variant, colPos1 As Variant, colPos2 As Variant, rowStart1 As Variant, rowStart2 As Variant)
    If UBound(colPos1) = UBound(colPos2) Then
        For I = rowStart1 To UBound(dataSet1, 1)
            For j = rowStart2 To UBound(dataSet2, 1)
                matchFlag = 0
                For k = 0 To UBound(colPos1)
                   If dataSet1(I, colPos1(k)) = dataSet2(j, colPos2(k)) Then
                        matchFlag = matchFlag + 1
                   End If
                Next k
                If matchFlag = (UBound(colPos1) + 1) Then
                    Debug.Print ("Match found in Workbook 1 at row " & I & "  and Workbook 2 at row " & j)
                End If
                
            Next j
        Next I
    End If
   
End Function

Function SheetToDataSet(workbookName As Variant, worksheetName As Variant) As Variant
    'SET PAGE CHARACTERISTICS
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
    'DECLARE VARIABLE
        Dim x_matrix As Range
        Dim x_copyrange As String
        Dim length, lastColumn As Integer
    'DEFINE VARIABLE
        Workbooks(workbookName).Worksheets(worksheetName).Activate
        length = 0
        lastColumn = 0
        For I = 1 To 10
            If length < Workbooks(workbookName).Worksheets(worksheetName).Cells(Rows.Count, I).End(xlUp).Row Then
                length = Workbooks(workbookName).Worksheets(worksheetName).Cells(Rows.Count, I).End(xlUp).Row
            End If
            If lastColumn < Workbooks(workbookName).Worksheets(worksheetName).Cells(I, Columns.Count).End(xlToLeft).Column Then
                lastColumn = Workbooks(workbookName).Worksheets(worksheetName).Cells(I, Columns.Count).End(xlToLeft).Column + 10
            End If
        Next I
        'Let x_copyrange = .Range(.Cells(1, 1), .Cells(length, lastColumn))
    'Return
        SheetToDataSet = Workbooks(workbookName).Worksheets(worksheetName).Range(Cells(1, 1), Cells(length, lastColumn))
End Function